SQL 多列 SELECT 动态排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4154522/
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
Dynamic Order by SELECT with multiple columns
提问by BradB
What is the correct MS SQL syntax to select multiple ORDER BY columns when the ORDER BY is based on a CASE statement?
当 ORDER BY 基于 CASE 语句时，选择多个 ORDER BY 列的正确 MS SQL 语法是什么？
The below works fine with single columns, but I need to sort by multiple columns:
以下适用于单列，但我需要按多列排序：
SELECT * FROM Products
ORDER BY
CASE WHEN @SortIndex = 1 THEN Price END ASC,
CASE WHEN @SortIndex = 2 THEN Price DESC, Title ASC END <-- problem line
回答by Asif Mulla
You could try this
你可以试试这个
SELECT * FROM Products
ORDER BY
CASE WHEN @SortIndex = 1 THEN Price END ASC,
CASE WHEN @SortIndex = 2 THEN Price END DESC,
CASE WHEN @SortIndex = 2 THEN Title END ASC
回答by Sadhir
@Brad. Pavel was suggesting the following (i think),
@布拉德。帕维尔提出以下建议（我认为），
DECLARE @query VARCHAR(MAX)
SET @query = 'SELECT * FROM Products
ORDER BY
'
IF (@SortIndex = 1)
SET @query =@query + ' Price ASC '
ELSE IF (@SortIndex = 2)
SET @query =@query + ' Price DESC, Title ASC '
sp_executesql @query
Why do you think that dynamic sql is not suited for complex stored procedures? Those are exactly the places where you should be using dynamic SQL as it can help reduce the complexity and solve problems such as parameter sniffing. I agree that dynamic sql has its downsides, but i would recommend that you at least give it a try if it works for you.
为什么你认为动态sql不适合复杂的存储过程？这些正是您应该使用动态 SQL 的地方，因为它可以帮助降低复杂性并解决参数嗅探等问题。我同意动态 sql 有其缺点，但我建议您至少尝试一下，如果它适合您。
回答by yrushka
Try this as a lower impact on server performance - assuming you have only 2 values at @SortIndex (1 and 2). If not, extend your If with more conditions.
尝试将其作为对服务器性能的较低影响 - 假设您在 @SortIndex（1 和 2）处只有 2 个值。如果没有，请使用更多条件扩展您的 If。
If @SortIndex = 1
BEGIN
SELECT * FROM Products ORDER BY Price ASC
END
ELSE
BEGIN
SELECT * FROM Products ORDER BY Price DESC, TITLE ASC
END
回答by Pavel Urban?ík
Do this ... and say goodbye to your performance. Unfortunately the best solution is to use dynamic sql.
这样做......并告别你的表现。不幸的是，最好的解决方案是使用动态 sql。
回答by Rick Savoy
You could rewrite this:
你可以重写这个：
ORDER BY
CASE WHEN @SortIndex = 1 THEN Price END ASC,
CASE WHEN @SortIndex = 2 THEN Price DESC, Title ASC END
as
作为
CASE WHEN @SortIndex = 1 THEN Price END ASC,
CASE WHEN @SortIndex = 2 THEN Price DESC END,
Title ASC
This adds the added sort column to ALL cases but in my situation, that's what I wanted.
这将添加的排序列添加到所有情况，但在我的情况下，这就是我想要的。
You can also do this (just for example):
您也可以这样做（仅作为示例）：
CASE WHEN @SortIndex = 1 THEN Price END ASC,Title ASC,
CASE WHEN @SortIndex = 2 THEN Price DESC END
