(一)《SQL进阶教程》学习记录--CASE (一)《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

【(一)《SQL进阶教程》学习记录--CASE】