SQL Server：带有NULL值的ORDER BY变量列CASE语句

I've got a query that can spit out results sorted by a variety of columns, but I need to be able to handle null values in the sorted columns. Any help would be greatly appreciated. Here is an example:

我有一个查询可以吐出按各种列排序的结果，但我需要能够处理排序列中的空值。任何帮助将不胜感激。这是一个例子：

Table [People]
Columns [Name], [Birthday] (NULLABLE)

Query where @Sort is an int designating which column to sort on, and @pageStart and @pageEnd are ints telling the query which results to return. (I am returning only a selection of the rows, so I am using a [RowNum] column nested in a CTE. There is also other processing happening, but I'm removing it for simplicity.):

查询@Sort是指定要排序的列的int，@ pageStart和@pageEnd是告知查询返回结果的查询。（我只返回选择的行，所以我使用嵌套在CTE中的[RowNum]列。还有其他处理发生，但我为了简单起见将其删除。）：

;with results as(
SELECT [Name], [Birthday], ROW_NUMBER() OVER  (ORDER BY 
         CASE WHEN @Sort = 0 THEN [Name] END,
         CASE WHEN @Sort = 2 THEN [Birthday] END,
         CASE WHEN @Sort = 1 THEN [Name] END DESC,
         CASE WHEN @Sort = 3 THEN [Birthday] END DESC) AS RowNum
FROM [People]
)
SELECT [Name], [Birthday]
FROM results
WHERE RowNum BETWEEN @pageStart AND @pageEnd
--ORDER RowNum
--The last order by doesn't seem to be needed

I know that nulls can be handled with a statement such as:

我知道可以使用如下语句处理null：

ORDER BY (CASE WHEN [columnName] is NULL THEN 1 ELSE 0 END), [columnName]

I'm having a hard time applying that to the query I am working with... any help would be greatly appreciated! Let me know if I can clarify anything.

我很难将其应用于我正在使用的查询...任何帮助将不胜感激！如果我能澄清任何事情，请告诉我。

3 个解决方案

#1

It seems like you're mostly there. Instead of a CASE...WHEN...END statement you could instead use ISNULL()

看起来你大部分都在那里。而不是CASE ... WHEN ... END语句你可以改为使用ISNULL（）

You'll need to choose relevant values from the same datatype, but for @Sort = 0 for example you could use

您需要从相同的数据类型中选择相关的值，但对于@Sort = 0，您可以使用

CASE WHEN @Sort = 0 THEN ISNULL([Name], '') END,

It also looks like you could condense you sequence of CASE...WHEN...END statements into one more like

看起来你也可以将CASE ... WHEN ... END语句的序列浓缩成一个更像

CASE @Sort
    WHEN 0 THEN ISNULL([Name], '')
    WHEN 2 THEN ISNULL([Birthday], 0)
...
...
END

#2

You could combine the sort parameter with the null check in the CASE expressions:

您可以将sort参数与CASE表达式中的空检查结合使用：

;with results as(
SELECT [Name], [Birthday], ROW_NUMBER() OVER  (ORDER BY 
       CASE WHEN @Sort = 0 AND [Name] IS NULL THEN 1 ELSE 0 END, [Name],
       CASE WHEN @Sort = 2 AND [Birthday] IS NULL THEN 1 ELSE 0 END, [Birthday],
       CASE WHEN @Sort = 1 AND [Name] IS NULL THEN 1 ELSE 0 END, [Name] DESC,
       CASE WHEN @Sort = 3 AND [Birthday] IS NULL THEN 1 ELSE 0 END, [Birthday] DESC)As RowNum
FROM [People]
)
SELECT [Name], [Birthday]
FROM results
WHERE RowNum BETWEEN @pageStart AND @pageEnd

#3

Your query is good in terms of code re-use. Unfortunately it's also poor in terms of optimisability. (Is that even a real word?)

您的查询在代码重用方面很好。不幸的是，它在可优化性方面也很差。（这甚至是一个真实的词吗？）

For this single query, the optimiser needs to build a single execution plan. It can't, for example, switch between different indexes for the ordering. Instead it will have to 'manually' re-order the data as your parameters change.

对于此单个查询，优化器需要构建单个执行计划。例如，它不能在排序的不同索引之间切换。相反，它必须在参数更改时“手动”重新排序数据。

This is especially relevant in paging. With a suitable index, the optimiser can use a range seek to jump straight to the rows you need. In the case of your single query, the effect will be to actually process the whole table, ordering appropriately, then jump to the appropriate records. (The result of having a single plan to suit all parameters.) For any significant amount of data, that is an extermely large overhead.

这在分页中尤其重要。使用合适的索引，优化器可以使用范围搜索直接跳转到您需要的行。在单个查询的情况下，效果将是实际处理整个表，适当排序，然后跳转到适当的记录。（具有适合所有参数的单一计划的结果。）对于任何大量数据，这是非常大的开销。

For this reason, it's actually often much more performant to approach this with Dynamic SQL. This allows each different ordering to have it's own execution plan. And then each plan case use the index most appropriate to those needs.

出于这个原因，使用Dynamic SQL来实现这一点通常要高效得多。这允许每个不同的排序具有它自己的执行计划。然后每个计划案例都使用最适合这些需求的索引。

#1