排序依据中的 SQL 排序依据

发布于 2024-10-05 05:36:52 字数 822 浏览 4 评论 0原文

所以我有两个表，问题和答案，由多对多关系表 QuestionsAnswers 连接。问题有一个排序列，允许我控制它们向用户显示的方式，而 QuestionsAnswers 也有一个排序列，允许我控制每个问题的答案的顺序。我的问题是我正在尝试编写一个 SQL 查询，该查询将选择所有问题及其答案，首先按 Question.Sort 排序，然后按 QuestionsAnswers.Sort 排序。两个排序列都可以为空，并且这些列中的整数应优先于空值。

我可以让 Order By Questions.Sort 正常工作，但是一旦我添加到第二个排序列中，它就会出错。例如，如果问题的 Sort = 0 意味着它应该是显示的第一个问题，但对其答案没有任何偏好，则它将在 Questions.Sort = null 且 QuestionsAnswers.Sort = 0 的 QuestionsAnswers 行下方排序如果这可行的话，

任何提示或想法都会很棒。

编辑：

SELECT
    Q.Id AS QuestionId,
    Q.Name AS Question,
    A.Id AS AnswerId,
    A.Text AS Answer
FROM
    dbo.Questions AS Q
INNER JOIN
    dbo.QuestionsAnswers AS QA
        ON Q.Id = QA.QuestionId
INNER JOIN
    dbo.Answers AS A
        ON QA.AnswerId = A.Id
ORDER BY
    ISNUMERIC(Q.Sort) DESC,
    Q.Sort,
    Q.Id,
    A.Text;

原文

So I have two tables, Questions and Answers, joined by a many to many relation table, QuestionsAnswers. Questions have a Sort column that allows me to control how they are displayed to the user, and QuestionsAnswers also has a sort column that allows me to control the order of the answers for each Question. My problem is that I am trying to write a SQL query that will select all the Questions and their Answers ordered first by Question.Sort and then by QuestionsAnswers.Sort. Both sort columns are nullable and integers in these columns should take precedence over nulls.

I can get the Order By Questions.Sort to work fine but once I add in the second sort column, it goes awry. For instance, if a Question has a Sort = 0 meaning it should be the first Question displayed but does not have any preference for its Answers, it will get ordered below a QuestionsAnswers row that has Questions.Sort = null and QuestionsAnswers.Sort = 0.

Any tips or thoughts if this is even workable would be great.

edit:

SELECT
    Q.Id AS QuestionId,
    Q.Name AS Question,
    A.Id AS AnswerId,
    A.Text AS Answer
FROM
    dbo.Questions AS Q
INNER JOIN
    dbo.QuestionsAnswers AS QA
        ON Q.Id = QA.QuestionId
INNER JOIN
    dbo.Answers AS A
        ON QA.AnswerId = A.Id
ORDER BY
    ISNUMERIC(Q.Sort) DESC,
    Q.Sort,
    Q.Id,
    A.Text;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

青瓷清茶倾城歌 2024-10-12 05:36:52

ORDER BY COALESCE(Questions.Sort, 999999999), COALESCE(Answers.Sort, 999999999)

ORDER BY COALESCE(Questions.Sort, 999999999), COALESCE(Answers.Sort, 999999999)

回复收藏 0 原文

一瞬间的火花 2024-10-12 05:36:52

据我所知，这样的事情应该可以解决问题。除非我错过了什么。

select questions.*, answers.* 
from questionsanswers qa
inner join questions
    on questions.idquestions = qa.questionid
inner join answers
    on answers.idanswers = qa.answerid
order by isnull(questions.sort), questions.sort, isnull(qa.sort), qa.sort

From what I can see something like this should do the trick. Unless I am misssing something.

select questions.*, answers.* 
from questionsanswers qa
inner join questions
    on questions.idquestions = qa.questionid
inner join answers
    on answers.idanswers = qa.answerid
order by isnull(questions.sort), questions.sort, isnull(qa.sort), qa.sort

回复收藏 0 原文

~没有更多了~