SQL 带有 Desc/Asc 排序的 Order By 子句的 Case 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25949837/
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 Desc/Asc sort
提问by volume one
SELECT *
FROM
TableName
WHERE
ORDER BY
CASE @OrderByColumn
WHEN 1 THEN Forename
WHEN 2 THEN Surname
END;
I have a statement like above which lets me dynamically choose how to order the results of a query. However, how do I specify that I want the Forename ordered DESCand the Surname ASC?
我有一个像上面这样的语句，它让我动态地选择如何对查询结果进行排序。但是，如何指定我想要 Forename 排序DESC和 Surname ASC？
回答by dasblinkenlight
You need to split your ORDER BYin two parts:
您需要将您的内容ORDER BY分为两部分：
SELECT *
FROM
TableName
WHERE
ORDER BY
(CASE @OrderByColumn
WHEN 1 THEN Forename
END) DESC -- Forename --> descending
, (CASE @OrderByColumn
WHEN 2 THEN Surname
END) ASC -- Surname --> ascending
回答by Gordon Linoff
You need two clauses in the order by:
您需要在 中的两个子句order by：
ORDER BY (CASE WHEN @OrderByColumn = 1 and @Dir = 'ASC' THEN Forename
WHEN @OrderByColumn = 2 and @Dir = 'ASC' THEN Surname
END) ASC,
(CASE WHEN @OrderByColumn = 1 and @Dir = 'DESC' THEN Forename
WHEN @OrderByColumn = 2 and @Dir = 'DESC' THEN Surname
END) DESC
回答by nagnath
another example:
另一个例子：
SELECT * FROM dbo.Employee
ORDER BY
CASE WHEN Gender='Male' THEN EmployeeName END Desc,
CASE WHEN Gender='Female' THEN Country END ASC
more details ...http://codechef4u.com/post/2015/04/07/order-by-clause-with-case-expressions-case-statement
更多细节... http://codechef4u.com/post/2015/04/07/order-by-clause-with-case-expressions-case-statement
