Geek asks and answers

SQL: case statement in order by condition

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

I see the link below the link below:

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:

BusinessEntityID, SalariedFlag

7.1

5.1

3,1

1,1

2.0

4.0

6.0

8.0

Can someone explain why records with the same salariedFlag are next to each other, and why salariedFlag = 1 piece is above the piece of salariedFlag = 0?

+3
source share
4 answers

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

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 , .
, SalariedFlag = 1 BusinessEntityID. , nulls , SalariedFlag!= 1 .

, , , : , SalariedFlag = 0 BusinessEntityID. , BusinessEntityID.

, SalariedFlag!= 0 . SalariedFlag = 1, .

SalariedFlag 0 1, () :

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

, , CASE WHEN SELECT:

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) , , SalariedFlag, , 2, , , , .

NULL , DESC, .

Live test: 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

+2

BusinessEntityID, 1, BusinessEntityID, 0. , CASE null, SalariedFlag 1, 0 1.

+1
source

Try including case statements in the list of values ​​you select back. This should help clarify what values ​​are actually happening.

0
source

More articles:


All Articles