SQL 自定义排序依据 [英] SQL Custom Order By

问题描述

我不明白为什么这不起作用:

I can't understand why this doesn't work:

select distinct a.QuestionID,a.QuestionName,b.AnswerID,b.AnswerName
    from @TempExportList a
    join tblAnswers b
    on a.QuestionID = b.QuestionID
    where a.PaperID=@PaperID
    order by (case when a.QuestionName='A' then 0
                   when a.QuestionName='B' then 1
                   else a.QuestionID
              end)

我收到以下错误 -

ORDER BY 项必须出现在选择列表，如果 SELECT DISTINCT 是指定.

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

但这工作正常:

select distinct a.QuestionID,a.QuestionName,b.AnswerID,b.AnswerName
    from @TempExportList a
    join tblAnswers b
    on a.QuestionID = b.QuestionID
    where a.PaperID=@PaperID
    order by a.QuestionID

推荐答案

错误信息完美地说明了问题.

The error message explains the problem perfectly.

在第一个示例中，ORDER BY 项 -- CASE WHEN ... END -- 没有出现在 SELECT 列表中.

In the first example the ORDER BY item -- CASE WHEN ... END -- doesn't appear in the SELECT list.

在第二个示例中，ORDER BY 项 -- a.QuestionID -- 确实出现在 SELECT 列表中.

In the second example the ORDER BY item -- a.QuestionID -- does appear in the SELECT list.

要修复第一个示例，您需要执行以下操作:

To fix the first example you'll need to do something like this:

SELECT DISTINCT a.QuestionID, a.QuestionName, b.AnswerID, b.AnswerName,
                CASE WHEN a.QuestionName = 'A' THEN 0
                     WHEN a.QuestionName = 'B' THEN 1
                     ELSE a.QuestionID
                END
FROM @TempExportList AS a
    JOIN tblAnswers AS b
        ON a.QuestionID = b.QuestionID
WHERE a.PaperID = @PaperID
ORDER BY CASE WHEN a.QuestionName = 'A' THEN 0
              WHEN a.QuestionName = 'B' THEN 1
              ELSE a.QuestionID
         END

这篇关于SQL 自定义排序依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！