Order BY CASE with UNION ALL表达式TSQL

Question

我想按文本列的字母顺序排序，但将空值放在最后。

在大多数情况下，我可以通过ORDER BY CASE WHEN myCol is null then 0 else 1 end DESC, myCol ASC很好地完成这项工作

但是我遇到了一种特定的场景，MSSQL不喜欢它--当我试图对结果进行联合排序时。

请考虑以下内容：

   Select 'b' as word
UNION ALL 
select null as word
UNION ALL 
select 'a' as word 
ORDER BY word ASC

这是有效的语法，但没有正确地对空值进行排序。

如果我应用下面的逻辑，我会得到一个错误：

Select 'b' as word
UNION ALL 
select null as word
UNION ALL 
select 'a' as word
order by case when word is null then 0 else 1 end desc, word asc

我知道如果我使用子查询或查询表/视图(如下所示)，它会工作，但我有奇怪的理由避免使用它，而且我也很好奇

select * from (
Select 'b' as word
UNION ALL 
select null as word
UNION ALL 
select 'a' as word
  ) a

order by case when word is null then 0 else 1 end desc, word asc

我可以在union (在中间的示例中)之后向ORDER BY子句添加什么来使其工作？

Here's a SQLfiddle if you'd like to see for yourself

Answer 1

有趣的是。

这就是我会怎么做。

Select 'b' as word,  0 AS ItsNull
UNION ALL 
select null as word, 1 AS ItsNull
UNION ALL 
select 'a' as word, 0 AS ItsNull
order by ItsNull, word ASC;

返回：

word ItsNull
---- -----------
a    0
b    0
NULL 1

Answer 2

如果出于某种原因，您必须避免使用CTE和subquery，则可以为此添加一个虚拟排序列，并按如下方式执行排序in-line：

 --Follows a pseudo code to add a dummy column
Select word, CASE WHEN word is null THEN 0 ELSE 1 END as sortcol
from Words
UNION ALL 
select  word, CASE WHEN word is null THEN 0 ELSE 1 END 
from Words
UNION ALL 
select  word, CASE WHEN word is null THEN 0 ELSE 1 END 
from Words
order by sortcol desc, word asc