SQL进阶之CASE表达式
CASE 表达式
越前须知（雾）
- 本系列参考《SQL进阶教程》1，DBMS选用MySQL。
- 本系列不涉及数据库安装与基础语句，对初学者存在一定门槛；基础知识建议阅读《SQL必知必会（第四版）》与《SQL基础教程（第二版）》。
CASE表达式概述
- 基本写法
-- 简单表达式
case sex
when '1' then '男'
when '2' then '女'
else '其他' end
-- 搜索表达式
case when sex = '1' then '男'
when sex = '2' then '女'
else '其他' end
- 注意事项
- 各条件分支返回数据类型须一致
- 记得写 ELSE 和 END
具体应用
下面按照 Q&A 形式提供解法。
转化已有编号方式并统计
A. 汇总原有编号
- 创表
CREATE TABLE PopTbl
(pref_name VARCHAR(32) PRIMARY KEY,
population INTEGER NOT NULL);
INSERT INTO PopTbl VALUES('德岛', 100);
INSERT INTO PopTbl VALUES('香川', 200);
INSERT INTO PopTbl VALUES('爱媛', 150);
INSERT INTO PopTbl VALUES('高知', 200);
INSERT INTO PopTbl VALUES('福冈', 300);
INSERT INTO PopTbl VALUES('佐贺', 100);
INSERT INTO PopTbl VALUES('长崎', 200);
INSERT INTO PopTbl VALUES('东京', 400);
INSERT INTO PopTbl VALUES('群马', 50);
- 文本对应
Q：将“北海道、青森”各地名按照高一级的地区单位分组
A：用case 表达式分组
select case pref_name
when '德岛' then '四国'
when '香川' then '四国'
when '爱媛' then '四国'
when '高知' then '四国'
when '福冈' then '九州'
when '佐贺' then '九州'
when '长崎' then '九州'
else '其他' end as district,
sum(population) as sum_pop
from PopTbl
group by case pref_name
-- group by需要将整个case表达式复制到这里，原则上不能使用 district 别名，因为 group by 执行顺序先于 select
-- 但MySQL执行前先扫描 select，可使用 group by district
when '德岛' then '四国'
when '香川' then '四国'
when '爱媛' then '四国'
when '高知' then '四国'
when '福冈' then '九州'
when '佐贺' then '九州'
when '长崎' then '九州'
else '其他' end;
- 数字区间对应
Q：按照人口将城市划分等级
A：用case表达式划分数字区间
select case when population < 100 then '01'
when population >= 100 and population < 200 then '02'
when population >= 200 and population < 300 then '03'
when population >= 300 then '04'
else null end as pop_class,
sum(population) as sum_pop
from PopTbl
group by pop_class
order by pop_class;
B. 行列转换
- 创表
CREATE TABLE PopTbl2
(pref_name VARCHAR(32),
sex CHAR(1) NOT NULL,
population INTEGER NOT NULL,
PRIMARY KEY(pref_name, sex));
INSERT INTO PopTbl2 VALUES('德岛', '1', 60 );
INSERT INTO PopTbl2 VALUES('德岛', '2', 40 );
INSERT INTO PopTbl2 VALUES('香川', '1', 100);
INSERT INTO PopTbl2 VALUES('香川', '2', 100);
INSERT INTO PopTbl2 VALUES('爱媛', '1', 100);
INSERT INTO PopTbl2 VALUES('爱媛', '2', 50 );
INSERT INTO PopTbl2 VALUES('高知', '1', 100);
INSERT INTO PopTbl2 VALUES('高知', '2', 100);
INSERT INTO PopTbl2 VALUES('福冈', '1', 100);
INSERT INTO PopTbl2 VALUES('福冈', '2', 200);
INSERT INTO PopTbl2 VALUES('佐贺', '1', 20 );
INSERT INTO PopTbl2 VALUES('佐贺', '2', 80 );
INSERT INTO PopTbl2 VALUES('长崎', '1', 125);
INSERT INTO PopTbl2 VALUES('长崎', '2', 125);
INSERT INTO PopTbl2 VALUES('东京', '1', 250);
INSERT INTO PopTbl2 VALUES('东京', '2', 150);
Q：对各地区不同性别人口进行汇总
A：select子句中用case表达式分列
select pref_name,
sum(case when sex = '1' then population else null end) as '男',
sum(case when sex = '2' then population else null end) as '女'
from PopTbl2
group by pref_name; -- 使用了聚合函数，需要对pref_name分组
Q：生成以下表格
A：select子句中用case表达式分列
select sex as '性别', sum(population) as '全国',
sum(case when pref_name = '德岛' then population else null end) as '德岛',
sum(case when pref_name = '香川' then population else null end) as '香川',
sum(case when pref_name = '爱媛' then population else null end) as '爱媛',
sum(case when pref_name = '高知' then population else null end) as '高知',
sum(case when pref_name in ('德岛','香川','爱媛','高知')
then population else null end) as '四国汇总'
from PopTbl2
group by sex; -- 对除聚合函数以外所有字段进行分组
用CHECK约束定义多个列的条件关系
- 创表
Q：限制女性员工工资不超过20万
A：在创表时加入限制
create table TestSal
(sex char(1) not null,
salary integer,
constraint check_salary check
-- check_salary为约束名称，check = 1,可插入这条数据
(case when sex = '2'
then (case when salary <= 200000 then 1 else 0 end)
else 1 end = 1));
INSERT INTO TestSal VALUES(1, 200000);
INSERT INTO TestSal VALUES(1, 300000);
INSERT INTO TestSal VALUES(1, NULL);
INSERT INTO TestSal VALUES(2, 200000);
INSERT INTO TestSal VALUES(2, 300000); -- error
INSERT INTO TestSal VALUES(2, NULL);
INSERT INTO TestSal VALUES(1, 300000);
UPDATE子句的条件分支
- 创表
CREATE TABLE Salaries
(name VARCHAR(32) PRIMARY KEY,
salary INTEGER NOT NULL);
INSERT INTO Salaries VALUES('相田', 300000);
INSERT INTO Salaries VALUES('神崎', 270000);
INSERT INTO Salaries VALUES('木村', 220000);
INSERT INTO Salaries VALUES('齐藤', 290000);
Q：对不同区间的工资进行不同更新操作：30万以上降薪10%、25-28万加薪20%、其余保持不变
A：case表达式不同条件分支
update Salaries
set salary = case when salary > 300000 then salary * 0.9
when salary >= 250000 and salary < 280000 then salary * 1.2
else salary end;
表间匹配
- 创表
CREATE TABLE CourseMaster
(course_id INTEGER PRIMARY KEY,
course_name VARCHAR(32) NOT NULL);
INSERT INTO CourseMaster VALUES(1, '会计入门');
INSERT INTO CourseMaster VALUES(2, '财务知识');
INSERT INTO CourseMaster VALUES(3, '簿记考试');
INSERT INTO CourseMaster VALUES(4, '税务师');
CREATE TABLE OpenCourses
(month INTEGER ,
course_id INTEGER ,
PRIMARY KEY(month, course_id));
INSERT INTO OpenCourses VALUES(200706, 1);
INSERT INTO OpenCourses VALUES(200706, 3);
INSERT INTO OpenCourses VALUES(200706, 4);
INSERT INTO OpenCourses VALUES(200707, 4);
INSERT INTO OpenCourses VALUES(200708, 2);
INSERT INTO OpenCourses VALUES(200708, 4);
Q：生成以下数据表。
两种解法：
- IN
select course_name,
case when course_id in (select course_id from OpenCourses
where month = '200706') then '〇'
else 'X' end as '6月',
case when course_id in (select course_id from OpenCourses
where month = '200707') then '〇'
else 'X' end as '7月',
case when course_id in (select course_id from OpenCourses
where month = '200708') then '〇'
else 'X' end as '8月'
from CourseMaster;
- EXISTS
select CM.course_name,
case when exists (select O.course_id from OpenCourses as O
where month = '200706'
and CM.course_id = O.course_id) then '〇'
else 'X' end as '6月',
case when exists (select O.course_id from OpenCourses as O
where month = '200707'
and CM.course_id = O.course_id) then '〇'
else 'X' end as '7月',
case when exists (select O.course_id from OpenCourses as O
where month = '200708'
and CM.course_id = O.course_id) then '〇'
else 'X' end as '8月'
from CourseMaster as CM;
聚合函数
- 创表
CREATE TABLE StudentClub
(std_id INTEGER,
club_id INTEGER,
club_name VARCHAR(32),
main_club_flg CHAR(1),
PRIMARY KEY (std_id, club_id));
INSERT INTO StudentClub VALUES(100, 1, '棒球', 'Y');
INSERT INTO StudentClub VALUES(100, 2, '管弦乐', 'N');
INSERT INTO StudentClub VALUES(200, 2, '管弦乐', 'N');
INSERT INTO StudentClub VALUES(200, 3, '羽毛球', 'Y');
INSERT INTO StudentClub VALUES(200, 4, '足球', 'N');
INSERT INTO StudentClub VALUES(300, 4, '足球', 'N');
INSERT INTO StudentClub VALUES(400, 5, '游泳', 'N');
INSERT INTO StudentClub VALUES(500, 6, '围棋', 'N');
Q：查询每个学生的单一社团或多社团的主社团
A：单一社团即count(*)=1；多社团的主社团即代号为‘Y’
select std_id,
case when count(*) = 1
then max(club_id) -- 都要加max()，保证同一列数据类型统一
else max(case when main_club_flg = 'Y'
then club_id
else null end) end as main_club
from StudentClub
group by std_id;
比较不同列，取最大值
- 创表
CREATE TABLE Greatests
(key1 CHAR(1) PRIMARY KEY,
x INTEGER NOT NULL,
y INTEGER NOT NULL,
z INTEGER NOT NULL);
INSERT INTO Greatests VALUES('A', 1, 2, 3);
INSERT INTO Greatests VALUES('B', 5, 5, 2);
INSERT INTO Greatests VALUES('C', 4, 7, 1);
INSERT INTO Greatests VALUES('D', 3, 3, 8);
Q：横向比较统一行不同属性列，取最大值；如A（1，2，3）取最大值3
A：两种解法
- MySQL特有 Greatest() 函数
select key1, greatest(greatest(x,y),z) from Greatests;
- CASE表达式
select key1,
case when (case when x < y then y else x end) < z then z
else (case when x < y then y
else x end) end as greatest
from Greatests;
Q：将结果按照一定顺序排序
A：order by case
select key1,
case when (case when x < y then y else x end) < z then z
else (case when x < y then y
else x end) end as greatest
from Greatests
order by case key1 when 'B' then '1'
when 'A' then '2'
when 'D' then '3'
else '4' end;
MICK[日] 《SQL进阶教程》 ↩︎
1374
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
