Shulou(Shulou.com)06/01 Report--

-- 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.