SQL 动态订单方向
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1147763/
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 direction
提问by markiz
I writing a SP that accepts as parameters column to sort and direction.
我写了一个 SP，它接受作为参数列来排序和方向。
I don't want to use dynamic SQL.
我不想使用动态 SQL。
The problem is with setting the direction parameter.
问题在于设置方向参数。
This is the partial code:
这是部分代码：
SET @OrderByColumn = 'AddedDate'
SET @OrderDirection = 1;
…
ORDER BY
CASE WHEN @OrderByColumn = 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
WHEN @OrderByColumn = 'Visible' THEN CONVERT(varchar(2), Visible)
WHEN @OrderByColumn = 'AddedBy' THEN AddedBy
WHEN @OrderByColumn = 'Title' THEN Title
END
回答by Gary McGill
You could have two near-identical ORDER BYitems, one ASCand one DESC, and extend your CASEstatement to make one or other of them always equal a single value:
您可以有两个几乎相同的ORDER BY项目， oneASC和 one DESC，并扩展您的CASE语句以使它们中的一个或另一个始终等于单个值：
ORDER BY
CASE WHEN @OrderDirection = 0 THEN 1
ELSE
CASE WHEN @OrderByColumn = 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
WHEN @OrderByColumn = 'Visible' THEN CONVERT(varchar(2), Visible)
WHEN @OrderByColumn = 'AddedBy' THEN AddedBy
WHEN @OrderByColumn = 'Title' THEN Title
END
END ASC,
CASE WHEN @OrderDirection = 1 THEN 1
ELSE
CASE WHEN @OrderByColumn = 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
WHEN @OrderByColumn = 'Visible' THEN CONVERT(varchar(2), Visible)
WHEN @OrderByColumn = 'AddedBy' THEN AddedBy
WHEN @OrderByColumn = 'Title' THEN Title
END
END DESC
回答by gbn
You can simplify the CASE by using ROW_NUMBER which sorts your data and effectively converts it into a handy integer format. Especially since the question is tagged SQL Server 2005
您可以通过使用 ROW_NUMBER 来简化 CASE，它对您的数据进行排序并将其有效地转换为方便的整数格式。特别是因为问题被标记为 SQL Server 2005
This also expands easily enough to deal with secondary and tertiary sorts
这也很容易扩展到处理二级和三级排序
I've used multiplier to again simplify the actual select statement and reduce the chance of RBAR evaluation in the ORDER BY
我使用 multiplier 再次简化了实际的 select 语句并减少了 ORDER BY 中 RBAR 评估的机会
DECLARE @multiplier int;
SELECT @multiplier = CASE @Direction WHEN 1 THEN -1 ELSE 1 END;
SELECT
Columns you actually want
FROM
(
SELECT
Columns you actually want,
ROW_NUMBER() OVER (ORDER BY AddedDate) AS AddedDateSort,
ROW_NUMBER() OVER (ORDER BY Visible) AS VisibleSort,
ROW_NUMBER() OVER (ORDER BY AddedBy) AS AddedBySort,
ROW_NUMBER() OVER (ORDER BY Title) AS TitleSort
FROM
myTable
WHERE
MyFilters...
) foo
ORDER BY
CASE @OrderByColumn
WHEN 'AddedDate' THEN AddedDateSort
WHEN 'Visible' THEN VisibleSort
WHEN 'AddedBy' THEN AddedBySort
WHEN 'Title' THEN TitleSort
END * @multiplier;
回答by Alex Black
Here is an example:
下面是一个例子：
CREATE PROCEDURE GetProducts
(
@OrderBy VARCHAR(50),
@Input2 VARCHAR(30)
)
AS
BEGIN
SET NOCOUNT ON
SELECT Id, ProductName, Description, Price, Quantity
FROM Products
WHERE ProductName LIKE @Input2
ORDER BY
CASE
WHEN @OrderBy = 'ProductNameAsc' THEN ProductName
END ASC,
CASE
WHEN @OrderBy = 'ProductNameDesc' THEN ProductName
END DESC
END
From here:
从这里：
http://www.dominicpettifer.co.uk/Blog/21/dynamic-conditional-order-by-clause-in-sql-server-t-sql
http://www.domincpettifer.co.uk/Blog/21/dynamic-conditional-order-by-clause-in-sql-server-t-sql
Ascending and Descending actions need to be grouped into separate CASE statements, separated with a comma. In your server-side code/script make sure to append 'Asc' or 'Desc' onto the order by string, or you could have two Stored procedure input parameters for column name and order by direction if you want.
升序和降序操作需要分组到单独的 CASE 语句中，用逗号分隔。在您的服务器端代码/脚本中，确保按字符串将“Asc”或“Desc”附加到 order 中，或者如果需要，您可以为列名和 order by direction 使用两个存储过程输入参数。
回答by Arun Prasad E S
This works fine for me – (where, order by, direction,Pagination)
这对我来说很好 - ( where, order by, direction, Pagination)
parameters
@orderColumn int ,
@orderDir varchar(20),
@start int ,
@limit int
select * from items
WHERE (items.status = 1)
order by
CASE WHEN @orderColumn = 0 AND @orderdir = 'desc' THEN items.[category] END DESC,
CASE WHEN @orderColumn = 0 AND @orderdir = 'asc' THEN items.[category] END ASC,
CASE WHEN @orderColumn = 1 AND @orderdir = 'desc' THEN items.[category] END DESC,
CASE WHEN @orderColumn = 1 AND @orderdir = 'asc' THEN items.[category] END ASC,
CASE WHEN @orderColumn = 2 AND @orderdir = 'desc' THEN items.[category] END DESC,
CASE WHEN @orderColumn = 2 AND @orderdir = 'asc' THEN items.[category] END ASC
OFFSET @start ROWS FETCH NEXT @limit ROWS ONLY
回答by Vadim Ovchinnikov
More compact version of accepted answer, but as accepted answer this works fine only when result expressions after THENhave the same type.
接受答案的更紧凑版本，但作为接受答案，只有当结果表达式THEN具有相同类型时才可以正常工作。
ORDER BY
CASE @OrderDirection WHEN 0 THEN
CASE @sortColumn
WHEN 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
WHEN 'Visible' THEN CONVERT(varchar(2), Visible)
WHEN 'AddedBy' THEN AddedBy
WHEN 'Title' THEN Title
END
END ASC,
CASE @OrderDirection WHEN 1 THEN
CASE @sortColumn
WHEN 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
WHEN 'Visible' THEN CONVERT(varchar(2), Visible)
WHEN 'AddedBy' THEN AddedBy
WHEN 'Title' THEN Title
END
END DESC
