SQL进阶之CASE表达式
CASE 表达式越前须知（雾）CASE表达式概述具体应用转化已有编号方式并统计A. 汇总原有编号B. 行列转换
用CHECK约束定义多个列的条件关系UPDATE子句的条件分支表间匹配聚合函数比较不同列，取最大值
CASE 表达式
越前须知（雾）
本系列参考《SQL进阶教程》，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
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
;
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
(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);
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
)
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进阶教程》 ↩︎
