MS Sql: Conditional ORDER BY ASC/DESC 问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/758655/
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
MS Sql: Conditional ORDER BY ASC/DESC Question
提问by Shimmy Weitzhandler
I want to to make to make the ordering in my query conditional so if it satisfiess the condition it should be ordered by descending
我想使我的查询中的排序有条件，所以如果它满足条件，则应按降序排序
For instance:
例如：
SELECT * FROM Data ORDER BY SortOrder CASE WHEN @Direction = 1 THEN DESC END
回答by tpdi
Don't change the ASCor DESC, change the sign of the thing being sorted-by:
不要更改ASCor DESC，更改正在排序的事物的符号：
SELECT * FROM table
ORDER BY
CASE WHEN @Direction = 1 THEN -id else id END asc;
The OP asks:
OP问：
Guys, I am not the SQL Expert, please explain me what means the id and -id, does it controls the ordering direction?
伙计们，我不是 SQL 专家，请解释一下 id 和 -id 是什么意思，它控制排序方向吗？
id is just whatever column you're sorting by; -id is just the negation of that, id * -1. If you're sorting by more than one column, you'll need to negate each column:
id 就是您要排序的任何列；-id 只是那个的否定，id * -1。如果您按多列排序，则需要否定每一列：
SELECT * FROM table
ORDER BY
CASE WHEN @Direction = 1 THEN -id else id END
CASE WHEN @Direction = 1 THEN -othercolumn else othercolumn END ;
If you're ordering by a non numeric column, you'll need to find an expression that makes that column "negative"; writing a function to do that may help.
如果您按非数字列排序，则需要找到使该列“为负”的表达式；编写一个函数来做到这一点可能会有所帮助。
回答by Jeremy Giaco
SELECT *
FROM Data
ORDER BY
Case WHEN @Direction = 1 THEN SortOrder END DESC,
Case WHEN 1=1 THEN SortOrder END
回答by LarsW
You can also use a scheme which supports all column types:
您还可以使用支持所有列类型的方案：
SELECT <column_list> FROM <table>
ORDER BY
CASE WHEN @sort_order = 'ASC' AND @sort_column = '<column>' THEN <column> END ASC,
CASE WHEN @sort_order = 'DESC' AND @sort_column = '<column>' THEN <column> END DESC
SELECT <column_list> FROM <table>
ORDER BY
CASE WHEN @sort_order = 'ASC' AND @sort_column = '<column>' THEN <column> END ASC,
CASE WHEN @sort_order = 'DESC' AND @sort_column = '<column>' THEN <column> END DESC
回答by MikeW
I have done something like this
我做过这样的事情
select productId, InventoryCount,
case
when @Direction = 1 then InventoryCount
else -InventoryCount
end as "SortOrder"
order by 3
