SQL：在ORDER BY子句中使用CASE语句

Question

http://msdn.microsoft.com/zh-cn/library/ms181765.aspx

我从上述链接中找到了以下的sql:

SELECT BusinessEntityID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC
    ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;
GO

这是我得到的一个结果：

业务实体ID，是否薪资标识

7，1

5，1

3，1

1，1

2，0

4，0

6，0

8，0

有人能解释一下为什么具有相同“是否薪资”标识的记录相邻，以及为什么“是否薪资”标识=1的部分位于“是否薪资”标识=0部分之上吗？

- ZZZ

Answer 1

排序子句等同于以下内容，这可能更加明显：

ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID ELSE null END DESC
        ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID ELSE null END;

因此，第一个排序字段是BusinessEntityID（当SalariedFlag = 1时），或null。
这将使所有SalariedFlag = 0的行分组在一起，因为它们的第一个排序字段都是null。
SalariedFlag = 1的行将按BusinessEntityID排序。 看起来，在降序排序中，null会被最后排序，所以所有SalariedFlag != 1的行都排在最后。

这是主要的排序，对于次要排序，发生了很多相同的事情：
所有SalariedFlag = 0的行将按BusinessEntityID排序。 由于它们的主排序字段都是null，所以它们最终按BusinessEntityID排序。

所有SalariedFlag！= 0的行将与null的次要排序一起分组。 如果这些行的SalariedFlag = 1，则它们已经按照主排序排序。

如果SalariedFlag只能为0或1，则此排序可以（稍微）简化为：

ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC
        , BusinessEntityID;

- Adrian Pronk

Answer 2

通过在SELECT子句中包含CASE WHEN条件，您可以轻松地可视化为什么以那种方式排序：

SELECT BusinessEntityID, SalariedFlag,

CASE SalariedFlag WHEN 1 THEN BusinessEntityID END AS A,
CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END AS B

FROM Employee
ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC
    ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;

输出：

BUSINESSENTITYID  SALARIEDFLAG      A                 B
7                 1                 7                 (null)
5                 1                 5                 (null)
3                 1                 3                 (null)
1                 1                 1                 (null)
2                 0                 (null)            2
4                 0                 (null)            4
6                 0                 (null)            6
8                 0                 (null)            8

SalariedFlag为0的人聚在一起，原因是他们（看列A）都属于null，如果SalariedFlag有另一个值，比如数字2，那么该查询就不起作用了，具有相同标志的人不一定再次彼此相邻。

NULL首先排序，如果指定DESC，则会最后排序。

实时测试：http://www.sqlfiddle.com/ ！3 / 1b849 / 13

---

认识到该查询的CASE WHEN THEN使用整数类型的BusinessEntityID，您可以执行以下查询以实现相同的效果。由于它使用SalariedFlag作为分隔器，因此以下查询可能会更快，因为您没有在ORDER BY上使用条件语句。您可以切换SalariedFlag并对其进行排序。

-- just to visualize things up
SELECT BusinessEntityID, SalariedFlag,

    A = BusinessEntityID * SalariedFlag,
    B = BusinessEntityID * (SalariedFlag ^ 1) 

FROM Employee
ORDER BY A DESC, B 

-- in actual you do this:

SELECT BusinessEntityID, SalariedFlag

FROM Employee
ORDER BY BusinessEntityID * SalariedFlag DESC, BusinessEntityID * (SalariedFlag ^ 1) 

给定以下数据：

CREATE TABLE [Employee]
    ([BusinessEntityID] int, [SalariedFlag] int);

INSERT INTO [Employee]
    ([BusinessEntityID], [SalariedFlag])
VALUES
    (10, 1),
    (5, 1),
    (9, 1),
    (1, 1),
    (2, 0),
    (7, 0),
    (6, 0),
    (8, 0);

这是输出结果：

BUSINESSENTITYID  SALARIEDFLAG      A                 B
10                1                 10                0
9                 1                 9                 0
5                 1                 5                 0
1                 1                 1                 0
2                 0                 0                 2
6                 0                 0                 6
7                 0                 0                 7
8                 0                 0                 8

Live test: http://www.sqlfiddle.com/#!3/e65ac/2

- Michael Buen

Answer 3

您正在根据BusinessEntityID首先排序，当标志为1时，然后通过BusinessEntityID解决平局，当标志为0时。这是因为第一个CASE表达式在SalariedFlag不是1时评估为null，因此所有标有0标志的记录都移至标有1标志的记录之后。

- Sergey Kalinichenko

Answer 4

尝试将case语句包含在您选择返回的值列表中。这应该有助于澄清排序实际发生在哪些值上。

- GaTechThomas

---

SalariedFlag 不是我所指的。尝试包括 CASE SalariedFlag WHEN 1 THEN BusinessEntityID END，CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END。 - GaTechThomas