(一)《SQL进阶教程》学习记录-CASE - ⎝⎛CodingNote.cc ⎞⎠

(一)《SQL进阶教程》学习记录–CASE

2021 年 11 月 7 日
笔记
PostgreSQL

背景：最近用到统计之类的复杂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

Tags: PostgreSQL