T-SQL条件排序(T-SQL Conditional Order By)

我试图写一个存储过程返回对象的列表，由用户选择，并作为SQL参数传递的排序顺序和排序方向。

可以说我有产品与下列的表：PRODUCT_ID（INT），名（VARCHAR），价值（INT），CREATED_DATE（日期时间）和参数@sortDir和@sortOrder

我想这样做

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

我试着用case语句做，但有什么困难，因为数据类型不同。任何人有什么建议吗？

Answer 1:

CASE是一个返回值的表达式。它不是控制的流量，如IF 。而且你不能使用IF在查询中。

不幸的是，有一些限制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;

可以说是一个简单的解决方案（尤其是如果这变得更加复杂）是使用动态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;

另加上动态SQL，尽管所有散布关于它的制造恐慌的：你可以为每个排序变化的最佳方案，而不是一个单一的计划，将优化你首先发生于使用任何类型的变化。它还在近期的表现比较，我跑了进行最佳普遍：

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

Answer 2:

你需要一个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

有四种不同的条款免除类型之间转换的问题。

Answer 3:

这样做有多种方式。一种方法是：

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

您也可以使用动态SQL做到这一点。

Answer 4:

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

Answer 5:

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

话虽这么说，另一种方式在SQL服务器要做到这一点特别是表变量。这需要更多的开销和复杂性，但它也给你一个很大的灵活性。

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

一个缺点是这种方法是，你将需要添加一个case块为每您要订购的所有情况，但你必须这样做与不涉及动态SQL的任何解决方案。

我建议这个方法的唯一情况是，如果你有一个相对较小的数据集，你可以很方便地辨别搜索情况。

文章来源: T-SQL Conditional Order By