T-SQL条件顺序依据-Java 学习之路

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

假设我有一个包含以下列的产品表：product_id（int），name（varchar），value（int），created_date（datetime）和参数@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语句做，但由于数据类型不同而遇到了问题 . 有人有什么建议吗？

4 回答

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

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

回复于 2024-04-27T14:11:26+08:00

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

不幸的是， CASE 表达式存在一些限制，这使得执行您想要的操作变得很麻烦 . 例如， CASE 表达式中的所有分支必须返回相同的类型，或者可以隐式转换为相同的类型 . 我不会't try that with strings and dates. You also can'使用 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
回复于 2024-04-27T14:11:26+08:00

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

回复于 2024-04-27T14:11:26+08:00

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

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来完成它 .

回复于 2024-04-27T14:11:26+08:00

T-SQL条件顺序依据

4 回答

相关问题