(一)《SQL進階教程》學習記錄--CASE

冬先生發表於2021-11-07
SQL

背景：最近用到統計之類的複雜Sql比較多，有種“提筆忘字”的感覺，看書練習，舉一反三，鞏固加強。?

(一) 《SQL進階教程》學習記錄--CASE
(二) 《SQL進階教程》學習記錄--GROUP BY、PARTITION BY

1、語法

兩種寫法：簡單 CASE 表示式（simple case expression）、搜尋 CASE 表示式（searched case expression）

-- 簡單 CASE 表示式
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END

-- 搜尋 CASE 表示式
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END

-- 可以用in
CASE WHEN address IN ('石家莊', '邯鄲') THEN '河北'
WHEN address = '鄭州' THEN '河南'
ELSE '其他' END

例1：統計各省人數，people表結構如下

SELECT
CASE		
  WHEN area IN ( '石家莊', '邯鄲', '秦皇島' ) THEN '河北' 
  WHEN area IN ( '鄭州', '安陽' ) THEN '河南' 
  ELSE '其他' END as province,
  SUM ( population ) as population
  FROM people 
  GROUP BY province

看似沒毛病，其實是有坑的，不同資料庫是有區別的，這句在 PostgreSQL 和 MySQL 可以順利執行，在 Oracle、 DB2、SQL Server 會報錯province不存在，需要修改一下

SELECT province,SUM ( population ) as population from 
(SELECT
   CASE		
    WHEN area IN ( '石家莊', '邯鄲', '秦皇島' ) THEN '河北' 
    WHEN area IN ( '鄭州', '安陽' ) THEN '河南' 
    ELSE '其他' END as province , population 
    FROM people ) peoplesum 
    GROUP BY province

其實，對於使用過多個庫的開發人員，看錯誤也能八九不離十的寫出來，重點是下面這種通用SQL，以前還真沒Get過，就是把條件在寫一遍，可能是因為會變長吧，哈哈哈哈~~~嗝

SELECT
CASE		
  WHEN area IN ( '石家莊', '邯鄲', '秦皇島' ) THEN '河北' 
  WHEN area IN ( '鄭州', '安陽' ) THEN '河南' 
  ELSE '其他' END as province,
  SUM ( population ) as population
  FROM people 
  GROUP BY
  CASE		
    WHEN area IN ( '石家莊', '邯鄲', '秦皇島' ) THEN '河北' 
    WHEN area IN ( '鄭州', '安陽' ) THEN '河南' 
    ELSE'其他' END

以上三種寫法，結果一致

2、行轉列

高頻“考點”，study表結構如下

SELECT
 username,
 SUM ( CASE subject WHEN '語文' THEN fraction ELSE 0 END ) AS yuwen,
 SUM ( CASE subject WHEN '數學' THEN fraction ELSE 0 END ) AS shuxue,
 SUM ( CASE subject WHEN '英語' THEN fraction ELSE 0 END ) AS yingyu 
FROM
 study 
GROUP BY
 username

3、條件更新

公司受疫情影響，全員降薪，掙得多降的多。月薪>=10w，減20%，10w>月薪>=5,減10%，5w>月薪>=1w，減5%，月薪<1w，不變（畢竟生活太難了?），如表：萬萬不可分次執行，因為有的人工資降完又符合了更低檔的要求，再降就沒法活了。。。當然你可以從低到高執行三次，更簡單的如下：

update salaries set salary = CASE WHEN salary >= 100000 THEN salary*0.8
				  WHEN salary >= 50000 AND salary < 100000 THEN salary*0.9
				  WHEN salary >= 10000 AND salary < 50000 THEN salary*0.95
				  ELSE salary END;

4、巢狀CASE

學以致用，真實需求搞一把。按月分別統計連續兩個月高報警、低報警時長綜合。簡化如表：

SELECT 
  SUM(CASE to_char(create_time,'yyyy-MM') WHEN '2021-08' THEN (CASE alarm_level WHEN '高報' then time_span else 0 end) ELSE 0 END  ) as 本月高報,
  SUM(CASE to_char(create_time,'yyyy-MM') WHEN '2021-08' THEN (CASE alarm_level WHEN '低報' then time_span else 0 end) ELSE 0 END  ) as 本月低報,
  SUM(CASE to_char(create_time,'yyyy-MM') WHEN '2021-07' THEN (CASE alarm_level WHEN '高報' then time_span else 0 end) ELSE 0 END  ) as 上月高報,
  SUM(CASE to_char(create_time,'yyyy-MM') WHEN '2021-07' THEN (CASE alarm_level WHEN '低報' then time_span else 0 end) ELSE 0 END  ) as 上月低報
FROM alarm_info

相關文章