T-SQL 条件排序依据

T-SQL Conditional Order By

提问by RiceRiceBaby

I am trying to write a stored procedure that returns a list of object with the sort order and sort direction selected by the user and passed in as sql parameters.

我正在尝试编写一个存储过程，该过程返回一个对象列表，其中包含用户选择的排序顺序和排序方向，并作为 sql 参数传入。

Lets say I have a table of products with the following columns: product_id(int), name(varchar), value(int), created_date(datetime) and parameters @sortDir and @sortOrder

假设我有一个包含以下列的产品表：product_id(int)、name(varchar)、value(int)、created_date(datetime) 和参数 @sortDir 和 @sortOrder

I want to do something like

我想做类似的事情

select *
from Product
  if (@sortOrder = 'name' and @sortDir = 'asc') 
  then order by name asc
  if (@sortOrder = 'created_date' and @sortDir = 'asc') 
  then order by created_date asc
  if (@sortOrder = 'name' and @sortDir = 'desc') 
  then order by name desc
  if (@sortOrder = 'created_date' and @sortDir = 'desc') 
  then order by created_date desc

I tried do it with case statements but was having problems since the data types were different. Anyone got any suggestions?

我尝试用 case 语句来做，但由于数据类型不同而遇到问题。有人有什么建议吗？

回答by Aaron Bertrand

CASEis an expression that returns a value. It is not for control-of-flow, like IF. And you can't use IFwithin a query.

CASE是一个返回值的表达式。它不是用于控制流，例如IF. 并且您不能IF在查询中使用。

Unfortunately, there are some limitations with CASEexpressions that make it cumbersome to do what you want. For example, all of the branches in a CASEexpression must return the same type, or be implicitly convertible to the same type. I wouldn't try that with strings and dates. You also can't use CASEto specify sort direction.

不幸的是，CASE表达式有一些限制，这使得做你想做的事情变得很麻烦。例如，CASE表达式中的所有分支都必须返回相同的类型，或者可以隐式转换为相同的类型。我不会用字符串和日期来尝试。您也不能用于CASE指定排序方向。

SELECT column_list_please
FROM dbo.Product -- dbo prefix please
ORDER BY 
  CASE WHEN @sortDir = 'asc' AND @sortOrder = 'name' THEN name END,
  CASE WHEN @sortDir = 'asc' AND @sortOrder = 'created_date' THEN created_date END,
  CASE WHEN @sortDir = 'desc' AND @sortOrder = 'name' THEN name END DESC,
  CASE WHEN @sortDir = 'desc' AND @sortOrder = 'created_date' THEN created_date END DESC;

An arguably easier solution (especially if this gets more complex) is to use dynamic SQL. To thwart SQL injection you can test the values:

一个可以说更简单的解决方案（特别是如果这变得更复杂）是使用动态 SQL。要阻止 SQL 注入，您可以测试这些值：

IF @sortDir NOT IN ('asc', 'desc')
  OR @sortOrder NOT IN ('name', 'created_date')
BEGIN
  RAISERROR('Invalid params', 11, 1);
  RETURN;
END

DECLARE @sql NVARCHAR(MAX) = N'SELECT column_list_please
  FROM dbo.Product ORDER BY ' + @sortOrder + ' ' + @sortDir;

EXEC sp_executesql @sql;

Another plus for dynamic SQL, in spite of all the fear-mongering that is spread about it: you can get the best plan for each sort variation, instead of one single plan that will optimize to whatever sort variation you happened to use first. It also performed best universally in a recent performance comparison I ran:

动态 SQL 的另一个优点，尽管散布着所有关于它的恐惧：您可以获得每个排序变体的最佳计划，而不是一个单独的计划，该计划将优化您碰巧首先使用的任何排序变体。在我最近进行的性能比较中，它也普遍表现最好：

http://sqlperformance.com/conditional-order-by

http://sqlperformance.com/conditional-order-by

回答by Gordon Linoff

You need a case statement, although I would use multiple case statements:

您需要一个 case 语句，尽管我会使用多个 case 语句：

order by (case when @sortOrder = 'name' and @sortDir = 'asc' then name end)  asc,
         (case when @sortOrder = 'name' and @sortDir = 'desc' then name end) desc,
         (case when @sortOrder = 'created_date' and @sortDir = 'asc' then created_date end) asc,
         (case when @sortOrder = 'created_date' and @sortDir = 'desc' then created_date end) desc

Having four different clauses eliminates the problem of converting between types.

有四个不同的子句消除了类型之间的转换问题。

回答by Pertinent Observer

There are multiple ways of doing this. One way would be:

有多种方法可以做到这一点。一种方法是：

SELECT *
FROM
(
  SELECT
  ROW_NUMBER() OVER ( ORDER BY
  CASE WHEN @sortOrder = 'name' and @sortDir = 'asc' then name
  END ASC,
  CASE WHEN @sortOrder = 'name' and @sortDir = 'desc' THEN name
  END DESC,
  CASE WHEN i(@sortOrder = 'created_date' and @sortDir = 'asc' THEN created_date
  END ASC,
  CASE WHEN i(@sortOrder = 'created_date' and @sortDir = 'desc' THEN created_date
  END ASC) RowNum
  *
)
order by 
RowNum

You can also do it using dynamic sql.

您也可以使用动态 sql 来完成。

回答by ljh

declare @str varchar(max)
set @str = 'select * from Product order by ' + @sortOrder + ' ' + @sortDir
exec(@str)

回答by Pow-Ian

I stumbled on this when I was trying to do a similar thing, however the select statement I was creating includes a rather large and ugly string concatenation which severely limited my ability to use a dynamic SQL approach. If you have simple search cases, this is a decent solution.

我在尝试做类似的事情时偶然发现了这一点，但是我创建的 select 语句包含一个相当大且丑陋的字符串连接，这严重限制了我使用动态 SQL 方法的能力。如果您有简单的搜索案例，这是一个不错的解决方案。

That being said, another way to do this in SQL server specifically is with a table variable. This requires more overhead and complexity but it also gives you a lot of flexibility.

话虽如此，在 SQL Server 中执行此操作的另一种方法是使用表变量。这需要更多的开销和复杂性，但它也为您提供了很大的灵活性。

DECLARE @RawProducts TABLE (
    product_id int, 
    name varchar(50), 
    value int, 
    created_date datetime
)
INSERT INTO @RawProducts
SELECT * FROM [Product]

IF @sortOrder = 'name' AND @sortDir = 'asc' BEGIN
    SELECT * FROM @RawProducts
    ORDER BY [name] ASC
END

IF @sortOrder = 'name' AND @sortDir = 'desc' BEGIN
    SELECT * FROM @RawProducts
    ORDER BY [name] desc
END

IF @sortOrder = 'created_date' AND @sortDir = 'asc' BEGIN
    SELECT * FROM @RawProducts
    ORDER BY [created_date] ASC
END

IF @sortOrder = 'created_date' AND @sortDir = 'desc' BEGIN
    SELECT * FROM @RawProducts
    ORDER BY [created_date] desc
END

One drawback to this methodology is that you will need to add a case block for each and every case you want to order by but you would have to do that with any solution that does not involve dynamic SQL.

这种方法的一个缺点是，您需要为要排序的每个案例添加一个案例块，但您必须使用任何不涉及动态 SQL 的解决方案来做到这一点。

The only time I would recommend this method is if you have a relatively small data set and you have easy to discern search cases.

我唯一推荐这种方法的情况是，如果您的数据集相对较小，并且很容易辨别搜索案例。