问题描述
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:
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语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!