001 _ {Case ~~ of SQL Series ~~ When ~~ Then}

1,Equivalent judgment, equivalent to switch case

Case expression
When value1 then returnvalue1
When value2 then returnvalue2
When value3 then returnvalue3
Else defaultreturnvalue
End
For example:
Case level
When 1 then 'Uncle'
When 2 then 'Uncle two'
When 3 then 'Uncle'
End

It is equivalent to a switch and can only be equivalent.

 

  SelectUsername, Uncle level = (

2. Case Level
4. When1Then 'Uncle'
6. When2Then 'Uncle two'
8. When3Then 'Uncle'
10. End
12. )
14. FromUserinfo

 
Select username, Uncle level = (Case levelwhen 1 then 'Uncle 'when 2 then' second uncle 'when 3 then 'Uncle 'end) from userinfo
 

The result is :~~~

 

2. interval judgment

Case
When condition1 then returnvalue1
When condition 2 then returnvalue2
When Condition 3 then returnvalue3
Else defaultreturnvalue
End
Equivalent to if... Else if... Else .... (Interval judgment is supported)
 
Note: The data types returned after then must be consistent. The data types of returnvalue1, returnvalue2, and returnvalue3 must be consistent.

2. SelectUsername, age = (
4. Case
6. WhenAgeBetween28And30Then 'Uncle'
8. WhenAgeBetween25And27Then 'Big youth'
10. WhenAgeBetween20And24Then 'Sao year'
14. WhenAge <20Then 'Kid'
16. Else 'Baby'
18. End
20. )
22. FromUserinfo2

 
Select username, age = (case when age between 28 and 30 then 'Uncle 'When age between 25 and 27 then 'Young up' when age between 20 and 24 then' Sao Nian 'when age <20 then 'kid' else 'babe' end) from userinfo2
 

 

3. Practical cases

1. user level in general forums

Forum users
Case bbslevel
When 1 then 'creden'
When 2 then 'prawns'
When 3 then 'cainiao'
End

2. Count the total sales amount of each salesperson in the order table, and list the salesperson's name, total sales amount, and title (> gold medals,> silver medals,> bronze medals, otherwise normal)

3. E-commerce users have different levels based on their total consumption.

4. players in the gaming industry

 

Ticket No. Amount
RMB1 10
RMB2 20
RMB3-30
RMB4-10
Output the preceding table in the following format:
Bill No. Income and expenditure
RMB1 10 0
RMB2 20 0
RMB3 0 30
RMB4 0 10

 

2. SelectNumber,
4. (
6. Case
8. WhenAmount> 0ThenAmount
10. Else0
12. End
14. ) Income,
16. (
18. Case
20. WhenAmount <0Then ABS(Amount)
22. Else0
24. End
26. ) Expenditure
28. FromTest

 
Select number (case when amount> 0 then amountelse 0end), (case when amount <0 then ABS (amount) else 0end) from test