T-SQL 条件排序依据 [英] T-SQL Conditional Order By

问题描述

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

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.

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

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

我想做类似的事情

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 语句来做，但由于数据类型不同，所以遇到了问题.有人有什么建议吗?

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

推荐答案

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

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

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

Unfortunately, there are some limitations with CASE expressions that make it cumbersome to do what you want. For example, all of the branches in a CASE expression 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 CASE to specify sort direction.

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 注入，您可以测试这些值:

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

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 的另一个优点，尽管散布着所有关于它的恐惧:您可以获得每个排序变体的最佳计划，而不是一个将优化您碰巧使用的任何排序变体的单一计划第一的.在我最近进行的一次性能比较中，它也普遍表现最好:

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:

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

这篇关于T-SQL 条件排序依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！