The usage of SQL CASE
-- simple case function
Case sex
When'1' then 'male'
When'2' then 'female'
Else 'other' end
-- case search function
Case when sex ='1' then 'male'
When sex ='2' then 'female'
Else 'other' end
These two ways can achieve the same function. The writing of the simple case function is relatively simple, but compared with the case search function, there will be some functional limitations, such as writing decisions.
For example, in the following sql, you will never get the result of the "second category":
Case when col_1 in ('axiomagery') then 'category I'
When col_1 in ('a') then 'category II'
Else 'other' end
-
two。 Batch update data
-- assuming that there are only three options for the status, change all FStatus=3 updates from 1 to 5 to 9 for 5 FStatusdates 88 to 9
Update T_SUF_Barcode set FStatus= (case FStatus
When'3' then'1'
When '88' then' 55'
Else FStatus end)
Where FBarcode between '180121702150001' and' 180121702153000'
-
3. Express the status in the bar code table in Chinese
Select fstatus from T_SUF_Barcode T1 where FBarcode between '180121702150001' and' 180121702153000'
-
Fstatus
one
...
five
...
five
nine
...
-
Select t1.FBarcode, (case t1.FStatus
When 1 then 'normal'
When 5 then 'unknown'
When 9 then 'invalid' end) statu
From T_SUF_Barcode t1
Where FBarcode between '180121702150001' and' 180121702153000'
FBarcode statu
180121702150001 normal
180121702150002...
180121702150003 unknown
180121702150004...
180121702150011 null and void
180121702150012...
4. Piecewise statistics can be realized by combining sum with case.
If you now want to count the bar codes of various states in the above table, the sql statement is as follows:
Select
Sum (case t1.FStatus when 1 then 1 end) is normal
Sum (case t1.FStatus when 5 then 2 end) unknown
Sum (case when t1.FSTATUSION = 1 and t1.FSTATUSING 5 then 1 end) is invalidated
From T_SUF_Barcode T1 where FBarcode between '180121702150001' and' 180121702153000'
-- sum summation. When it is 5, it is updated to 2, and the sum meets the condition of 2. There are 1000 data that are normal, unknown and invalid.
Normal unknown invalidation
1000 2000 (1000 / 2) 1000
-
Select
Count (case t1.FStatus when 1 then 1 end) is normal
Count (case t1.FStatus when 5 then 1 end) unknown
Count (case when t1.FSTATUSION = 1 and t1.FSTATUSING 5 then 1 end) is invalidated
From T_SUF_Barcode T1 where FBarcode between '180121702150001' and' 180121702153000'
-- count the number of people who meet the conditions.
Normal unknown invalidation
1000 1000 1000
