SQL 进阶_01_CASE WHEN表达式
SQL进阶_01_CASE WHEN表达式
1、神奇的SQL
1.1 CASE 表达式
1.1.1 概述
CASE表达式是SQL里非常重要而且使用起来非常便利的技术，我们应该学会用它来描述条件分支；
CASE表达式有简单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
使用CASE的注意事项：
1、统一各分支返回的数据类型；
2、不要忘了写END；
3、养成写ELSE子句的习惯；
与END不同，ELSE子句是可选的，不写也不会出错。不写ELSE子句时，CASE表达式的执行结果是NULL。但是不写可能会造成“语法没有错误，结果却不对”这种不易追查原因的麻烦，所以最好明确地写上ELSE子句（即便是在结果可以为NULL的情况下）。
将已有编号方式转换为新的方式并统计：
■ 统计数据源表PopTbl
■ 统计结果
使用CASE表达式，则用如下所示的一条SQL语句就可以完成。为了便于理解，这里用县名（pref_name）代替编号作为GROUP BY的列。
--把县编号转换成地区编号(1)
SELECT CASE pref_name
WHEN ’德岛’ THEN ’四国’
WHEN ’香川’ THEN ’四国’
WHEN ’爱媛’ THEN ’四国’
WHEN ’高知’ THEN ’四国’
WHEN ’福冈’ THEN ’九州’
WHEN ’佐贺’ THEN ’九州’
WHEN ’长崎’ THEN ’九州’
ELSE’其他’END AS district,
SUM(population)
FROM PopTbl
GROUP BY CASE pref_name
WHEN ’德岛’ THEN ’四国’
WHEN ’香川’ THEN ’四国’
WHEN ’爱媛’ THEN ’四国’
WHEN ’高知’ THEN ’四国’
WHEN ’福冈’ THEN ’九州’
WHEN ’佐贺’ THEN ’九州’
WHEN ’长崎’ THEN ’九州’
ELSE ’其他’ END;
优化的写法：但是仅仅有比较少的DBMS支持，比如MySQL，Oracle就不支持这种写法。
--把县编号转换成地区编号(2)
SELECT CASE pref_name
WHEN ’德岛’ THEN ’四国’
WHEN ’香川’ THEN ’四国’
WHEN ’爱媛’ THEN ’四国’
WHEN ’高知’ THEN ’四国’
WHEN ’福冈’ THEN ’九州’
WHEN ’佐贺’ THEN ’九州’
WHEN ’长崎’ THEN ’九州’
ELSE ’其他’ END AS district,
SUM(population)
FROM PopTbl
GROUP BY district;--group by引用了select定义的列名
比如统计某医院从2021年7月1日到2022年7月1日新型冠状病毒单检和混检开立人次以及金额，这是我们使用聚集函数、CASE表达式以及GROUP BY进行分组就能很好的得到这个结果；
SQL
select
t.ordered 科室编码,
sum(case when t.item_code='250403091' then t.amount else 0 end) as "新型冠状病毒单检开立人次",
sum(case when t.item_code='250403091' then t.charges else 0 end) as "新型冠状病毒单检合计金额",
sum(case when t.item_code='250403091a' then t.amount else 0 end) as "新型冠状病毒混检开立人次",
sum(case when t.item_code='250403091a' then t.charges else 0 end) as "新型冠状病毒混检合计金额",
sum(t.amount) 人次合计,
sum(t.charges) 金额合计
from bill_item t
where t.charge_date > to_date('2021-07-01','yyyy-mm-dd')
and t.charge_date < to_date('2022-07-01','yyyy-mm-dd')
group by t.ordered_by;
运行结果
用CHECK约束定义多个列的条件关系：
CASE表达式和CHECK约束是很般配的一对组合。
在命题逻辑中，蕴含式（conditional）的逻辑表达式，记作P→Q。这里需要重点理解的是蕴含式和逻辑与（logical product）的区别。逻辑与也是一个逻辑表达式，意思是“P且Q”，记作P∧Q。
后面遇到好的例子再来完善
在UPDATE语句里进行条件分支：
salary
假设现在需要根据以下条件对该表的数据进行更新。
1．对当前工资为30万日元以上的员工，降薪10%。
2．对当前工资为25万日元以上且不满28万日元的员工，加薪20%。
分别执行下面两个UPDATE操作好像就可以做到，但这样的结果却是不正确的：
--大于300000的降薪百分之十
update luck.salary a set a.salary = a.salary * 0.9
where a.salary > '300000';
--大于250000并且小于300000的加薪百分之二十
update luck.salary a set a.salary = a.salary * 1.2
where a.salary > 250000 and a.salary < '300000';
分析
我们来分析一下不正确的原因。例如这里有一个员工，当前工资是30万日元，按“条件1”执行UPDATE操作后，工资会被更新为27万日元，但继续按“条件2”执行UPDATE操作后，工资又会被更新为32.4万日元。这样，本来应该被降薪的员工却被加薪了2.4万日元。
使用CASE
--优化
update luck.salary a set a.salary =
case when a.salary >= '300000' then a.salary * 0.9
when a.salary >= 250000 and a.salary < '300000' then a.salary * 1.2
else
a.salary
end;
运行结果
源码
create table luck.salary(
id number not null,
name varchar2(255),
salary decimal(12,3),
primary key(id)
);
insert into luck.salary values('1','小明','300000');
insert into luck.salary values('2','小红','240000');
insert into luck.salary values('3','小朱','320000');
insert into luck.salary values('4','小王','150000');
insert into luck.salary values('5','老王','390000');
insert into luck.salary values('6','小智','120000');
insert into luck.salary values('7','王二','90000');
insert into luck.salary values('8','小马','5000');
insert into luck.salary values('9','小赵','190000');
commit;
select * from luck.salary t;
--大于300000的降薪百分之十
update luck.salary a set a.salary = a.salary * 0.9
where a.salary >= '300000';
--大于250000并且小于300000的加薪百分之二十
update luck.salary a set a.salary = a.salary * 0.9
where a.salary >= 250000 and a.salary < '300000';
--优化
update luck.salary a set a.salary =
case when a.salary >= '300000' then a.salary * 0.9
when a.salary >= 250000 and a.salary < '300000' then a.salary * 1.2
else
a.salary
end;
需要注意的是，SQL语句最后一行的ELSE salary非常重要，必须写上。因为如果没有它，条件1和条件2都不满足的员工的工资就会被更新成NULL。这一点与CASE表达式的设计有关，在刚开始介绍CASE表达式的时候我们就已经了解到，如果CASE表达式里没有明确指定ELSE子句，执行结果会被默认地处理成ELSE NULL。现在大家明白笔者最开始强调使用CASE表达式时要习惯性地写上ELSE子句的理由了吧？
表之间的数据匹配：
与DECODE函数等相比，CASE表达式的一大优势在于能够判断表达式。也就是说，在CASE表达式里，我们可以使用BETWEEN、LIKE和<、>等便利的谓词组合，以及能嵌套子查询的IN和EXISTS谓词。因此，CASE表达式具有非常强大的表达能力。
要完成这样的设计：
、 、| course_name | 6月 | 7月 | 8月 | |
|---|---|---|---|---|
| 1 | 会计入门 | ○ | × | × |
| 2 | 财务知识 | × | × | ○ |
| 3 | 簿记考试 | ○ | × | × |
| 4 | 税务师 | ○ | ○ | ○ |
SQL
--表的匹配：使用IN谓词
SELECT course_name,
CASE WHEN course_id IN
(SELECT course_id FROM OpenCourses
WHERE month = 200706) THEN'○'
ELSE'×'END AS "6月",
CASE WHEN course_id IN
(SELECT course_id FROM OpenCourses
WHERE month = 200707) THEN'○'
ELSE'×'END AS "7月",
CASE WHEN course_id IN
(SELECT course_id FROM OpenCourses
WHERE month = 200708) THEN'○'
ELSE'×'END AS "8月"
FROM CourseMaster;
--表的匹配：使用EXISTS谓词
SELECT CM.course_name,
CASE WHEN EXISTS
(SELECT course_id FROM OpenCourses OC
WHERE month = 200706
AND OC.course_id = CM.course_id) THEN'○'
ELSE'×'END AS "6月",
CASE WHEN EXISTS
(SELECT course_id FROM OpenCourses OC
WHERE month = 200707
AND OC.course_id = CM.course_id) THEN'○'
ELSE'×'END AS "7月",
CASE WHEN EXISTS
(SELECT course_id FROM OpenCourses OC
WHERE month = 200708
AND OC.course_id = CM.course_id) THEN'○'
ELSE'×'END AS "8月"
FROM CourseMaster CM;
分析
无论使用IN还是EXISTS，得到的结果是一样的，但从性能方面来说，EXISTS更好。通过EXISTS进行的子查询能够用到“month, course_id”这样的主键索引，因此尤其是当表OpenCourses里数据比较多的时候更有优势。
重点：
1．在GROUP BY子句里使用CASE表达式，可以灵活地选择作为聚合的单位的编号或等级。这一点在进行非定制化统计时能发挥巨大的威力。
2．在聚合函数中使用CASE表达式，可以轻松地将行结构的数据转换成列结构的数据。
3．相反，聚合函数也可以嵌套进CASE表达式里使用。
4．相比依赖于具体数据库的函数，CASE表达式有更强大的表达能力和更好的可移植性。
5．正因为CASE表达式是一种表达式而不是语句，才有了这诸多优点。
小试牛刀：
1.1 多列数据的最大值：
通过GROUP BY子句对合适的列进行聚合操作，并使用MAX或MIN聚合函数就可以求出。那么，从多列数据里选出最大值该怎么做呢？
样本数据如下表所示。
greatests：
思考：先思考一下从表里选出x和y二者中较大的值的情况。此时求得的结果应该如下所示。
SQL
--求出x、y的最大值
select
t.key,
(case when x > y then x
else y end) as greatest
from luck.greatests t;
求出x和y二者中较大的值后，再试着将列数扩展到3列以上吧。这次求的是x、y和z三者中的最大值，因此结果应该如下所示。
SQL
select
t.key,
(case when (case when x > y then x else y end) > z then (case when x > y then x else y end)
else z end) as greatest
from luck.greatests t;
特别注意：
这里的《case when x > y then x else y end》作为一个条件和《z》去比较。
956
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
