SQL:order by子句中的case语句 [英] SQL: case statement in order by clause

问题描述

http://msdn.microsoft.com/en-us/library/ms181765.aspx

我从上方链接中看到以下sql:

I see the sql below from above link:

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

这是我得到的一个结果:

Here is one result I get:

BusinessEntityID，SalariedFlag

BusinessEntityID,SalariedFlag

7,1

5,1

3,1

1,1

2,0

4,0

6,0

8,0

谁能解释为什么具有相同salariedFlag的记录彼此相邻，为什么salariedFlag = 1块高于salariedFlag = 0块?

Could anyone explain why the records with same salariedFlag are next to each other and why salariedFlag=1 chunk is above the salariedFlag=0 chunk?

推荐答案

sort子句等效于以下内容，可能会更加明显:

The sort clause is equivalent to the following, which may be slightly more obvious:

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

因此，当SalariedFlag = 1或为null时，第一个排序字段是BusinessEntityID.
这会将SalariedFlag = 0的所有行组合在一起，因为它们都具有空的first sort字段.
SalariedFlag = 1的行将按BusinessEntityID排序.看起来空值最后以降序排序，因此所有SalariedFlag！= 1都倒数第一.

So the first sort field is the BusinessEntityID when SalariedFlag = 1, or null.
That will group all the rows where SalariedFlag = 0 together as they all have a null first sort field.
The rows that SalariedFlag = 1 wil be sorted by BusinessEntityID. It looks like nulls get sorted last in a descending sort so all the SalariedFlag != 1 go last.

这是主要类别，对于次要类别，发生的事情大致相同:
SalariedFlag = 0的所有行将按BusinessEntityID排序.由于它们的主要排序字段全为空，因此它们最终将按BusinessEntityID排序.

That's the major sort, for the secondary sort, much the same thing happens:
All the rows where SalariedFlag = 0 will be sorted by BusinessEntityID. Since their primary sort fields were all null, they will end up ordered by BusinessEntityID.

SalariedFlag！= 0的所有行都将以空二级顺序分组在一起.如果这些行的SalariedFlag = 1，那么它们将已经按照主要顺序进行了排序.

And all the rows where SalariedFlag != 0 will be grouped together with a null secondary ordering. If those rows had SalariedFlag = 1, then they would already have been sorted by the primary ordering.

如果SalariedFlag只能为0或1，则这种排序可以(略)简化为:

If SalariedFlag can only be 0 or 1 then this sort can be (slightly) simplified to:

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

这篇关于SQL:order by子句中的case语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！