SQL 具有多列和 Desc/Asc 排序的 Order By 子句的 CASE 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26048976/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CASE Statement for Order By Clause with Multiple Columns and Desc/Asc Sort
提问by volume one
Following on from my earlier question here Case statement for Order By clause with Desc/Asc sortI have a statement like this:
继我之前的问题 here Case statement for Order By 子句 with Desc/Asc sort我有这样的语句：
SELECT
*
FROM
TableName
WHERE
ORDER BY
CASE @OrderByColumn WHEN 1 THEN Forename END DESC,
CASE @OrderByColumn WHEN 2 THEN Surname END ASC
This works well, but sometimes I need more than column in the order by. I actually need something like this:
这很有效，但有时我需要的不仅仅是按顺序排列的列。我实际上需要这样的东西：
.....
ORDER BY
CASE @OrderByColumn WHEN 1 THEN Forename, Date, Location END DESC
I can't work out how to make the CASEstatement allow multiple columns in the THENpart.
我不知道如何使CASE语句允许部件中的多列THEN。
采纳答案by Tim Schmelter
Do you need this?
你需要这个吗？
ORDER BY
CASE @OrderByColumn WHEN 1 THEN Forename END DESC, Date, Location,
CASE @OrderByColumn WHEN 2 THEN Surname END ASC
回答by GolezTrol
You can write multiple cases, even if they all have the same condition.
您可以编写多个案例，即使它们都具有相同的条件。
ORDER BY
CASE @OrderByColumn WHEN 1 THEN Forename END DESC,
CASE @OrderByColumn WHEN 1 THEN Date END,
CASE @OrderByColumn WHEN 1 THEN Location END,
CASE @OrderByColumn WHEN 2 THEN Surname END ASC
Actually, you don't specify a column to sort by, but an expression.
实际上，您没有指定要排序的列，而是指定一个表达式。
The case statement returns null if the condition is not met, so actually it means:
如果条件不满足，case 语句返回 null，所以实际上它的意思是：
CASE @OrderByColumn WHEN 1 THEN Forename ELSE NULL END
So if @OrderByColumn is not 1 then the statement returns always NULL. That doesn't exclude it from sorting, by the way, but it puts all those rows together in the result, making 'SurName' the decisive sorting within that group of rows.
因此，如果@OrderByColumn 不是 1，则该语句始终返回 NULL。顺便说一下，这并没有将它排除在排序之外，而是将所有这些行放在结果中，使 'SurName' 成为该组行中的决定性排序。
