SQL进阶01 CASE表达式

01 引言

CASE表达式允许程序员在其程序代码中基于逻辑表达式选取一个值。CASE表达式分为简单CASE表达式 和搜索CASE表达式。

1. 简单CASE表达式

CASE sex
	WHEN 1 THEN '男'
	WHEN 0 THEN '女'
ELSE '其它' END

2. 搜索CASE表达式

CASE WHEN sex=1 THEN '男'
	 WHEN sex=0 THEN '女'
	 ELSE '其它' END

02 注意事项

1. 不要忘记写 END
2. 尽量加上ELSE条件
3. 注意返回类型一致

03 案例

3.1 已有编号方式转换成新方式统计

3.1.1 问题

根据上表统计得到下表

3.1.2 建表语句

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);

3.1.3 正确代码

SELECT * FROM poptbl;
SELECT
	CASE WHEN pref_name='巫溪'   THEN '重庆'
		 WHEN pref_name='巫山'   THEN '重庆'
         WHEN pref_name='九龙坡' THEN '重庆'
         WHEN pref_name='抚顺' THEN '辽宁'
         WHEN pref_name='鞍山' THEN '辽宁'
         WHEN pref_name='沈阳' THEN '辽宁'
         WHEN pref_name='武汉' THEN '湖北'
         WHEN pref_name='黄石' THEN '湖北'
         WHEN pref_name='恩施' THEN '湖北'
	ELSE '其它' END AS province,
    SUM(population) AS pop
FROM 
	poptbl
GROUP BY
	CASE WHEN pref_name='巫溪'   THEN '重庆'
		 WHEN pref_name='巫山'   THEN '重庆'
         WHEN pref_name='九龙坡' THEN '重庆'
         WHEN pref_name='抚顺' THEN '辽宁'
         WHEN pref_name='鞍山' THEN '辽宁'
         WHEN pref_name='沈阳' THEN '辽宁'
         WHEN pref_name='武汉' THEN '湖北'
         WHEN pref_name='黄石' THEN '湖北'
         WHEN pref_name='恩施' THEN '湖北'
	ELSE '其它' END;

分析

SQL是面向集合的语言。GROUP BY语句实现了对集合的划分。
上述GROUP BY 语句 将地区分成了 三个集合，如图所示。
此处忽略ELSE语句中的’其它’类别
SELECT 语句的CASE语句输出则是三个地方的人口总量：湖北；重庆；辽宁。

3.1.4 正确代码2

SELECT
	CASE WHEN pref_name IN ('巫溪','巫山','九龙坡') THEN '重庆'
		 WHEN pref_name IN ('抚顺','鞍山','沈阳') THEN '辽宁'
         WHEN pref_name IN ('武汉','黄石','恩施') THEN '湖北'
	ELSE '其它' END AS province,
    SUM(population) AS pop
FROM 
	poptbl
GROUP BY
	CASE WHEN pref_name IN ('巫溪','巫山','九龙坡') THEN '重庆'
		 WHEN pref_name IN ('抚顺','鞍山','沈阳') THEN '辽宁'
         WHEN pref_name IN ('武汉','黄石','恩施') THEN '湖北'
	ELSE '其它' END

分析

使用了IN谓词。

3.2 实现不同条件统计

比较简单，假设一个表是（name,sex）,下述SQL可以统计男生和女生的人数

SELECT 
	SUM(CASE WHEN sex=0 THEN 1 ELSE 0 END) pop_m,
	SUM(CASE WHEN sex=1 THEN 1 ELSE 0 END) pop_f
FROM
	tbl
GROUP BY sex

3.3 表之间数据匹配

3.3.1 问题

根据上述两表生成下图中的交叉表

图片来自SQL进阶教程

/* 表之间的数据匹配 */
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);

/* 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 * FROM OpenCourses OC
							WHERE OC.course_id=CM.course_id
								AND
                                  OC.month='200706') THEN  '⚪'
			   ELSE '×' END AS '6月'
		, CASE WHEN EXISTS
						( SELECT * FROM OpenCourses OC
							WHERE OC.course_id=CM.course_id
								AND
                                  OC.month='200707') THEN  '⚪'
			   ELSE '×' END AS '7月'
		, CASE WHEN EXISTS
						( SELECT * FROM OpenCourses OC
							WHERE OC.course_id=CM.course_id
								AND
                                  OC.month='200708') THEN  '⚪'
			   ELSE '×' END AS '8月'
FROM
CourseMaster CM

分析

IN 谓词 和 EXISTS 谓词区别

1. IN谓词生成了中间表，以中间表为主表进行遍历。本案列中EXISTS谓词将CourseMaster作为主表，按照EXISTS()中的条件按行遍历主表。
2. EXISTS 可以使用索引

3.4 CASE 表达式中使用聚合函数

3.4.1 主社团

/* 在CASE表达式中使用聚合函数 */
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');

/* 1. 获取只加入了一个社团的学生的社团 ID。*/
SELECT    std_id
		, club_id main_club_id
FROM 
	StudentClub
GROUP BY std_id
HAVING count(*)=1;

/* 2. 获取加入了多个社团的学生的主社团 ID。*/
SELECT    std_id
		, club_id main_club_id
FROM 
	StudentClub
WHERE main_club_flg='Y';

/* 3. 获取学生的主社团 ID,当学生只有一个社团时将该社团作为主社团*/
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_id
FROM 
	StudentClub
GROUP BY std_id

分析

使用GROUP BY 将数据进行分组。当该组的计数为1时说明该学生只有一个社团，使用MAX(club_id)提取出该社团的ID。当 计数大于1时，内层的CASE语句对该学生的社团集合进行了过滤，只保留主社团的ID。

3.4.2 教师

根据上表得出下表，即统计每门课程每个学生的老师姓名。
teacher2取值如下：

1. 一个老师 NULL
2. 两个老师 第二个老师姓名
3. 超过两个 More
   

CREATE TABLE Register
(
course_nbr INTEGER NOT NULL,
student_name CHAR ( 10 ) NOT NULL,
teacher_name CHAR ( 10 ) NOT NULL
);
INSERT INTO Register ( course_nbr , student_name , teacher_name )
VALUES ( 1 , 'Japser' , 'Yet' ),
( 1 , 'Japser' , 'Jucii' ),
( 1 , 'Japser' , 'Mole' ),
( 2 , 'Fue' , 'Orea' ),
( 2 , 'Fue' , 'Gucii' ),
( 2 , 'Fue' , 'Docde' ),
( 3 , 'Cc' , 'Riven' ),
( 4 , 'Dc' , 'Gucii' ),
( 4 , 'Dc' , 'Orea' );

/*选取每门课程每个学生的老师姓名*/
SELECT   course_nbr
		,student_name
        ,MIN(teacher_name) teacher_1
		,CASE WHEN COUNT(*)=1 THEN NULL
			 WHEN COUNT(*)=2 THEN MAX(teacher_name)
             WHEN COUNT(*)>2 THEN '~~MORE~~'
		 ELSE NULL END AS teacher_2
FROM
	Register
GROUP BY course_nbr,student_name;

分析

1. 对NULL 值处理
   MIN MAX AVG SUM 处理时忽略 NULL值
   COUNT(*) 包含 NULL COUNT(列名)不包含NULL

2. 对于某个集合 MAX()=MIN()意味着该集合只有一个元素。集合只有两个元素时，MAX() MIN()可以取出这两个值。

3.4.3 双重职务

给定下表，D表示主管，O表示高级职员

简化得到下表

CREATE TABLE role_tab (
person CHAR(5) NOT NULL,
role   CHAR(1) NOT NULL
);
INSERT INTO role_tab VALUES ('smith','O')
					,('smith','D')
                    ,('niu','O')
                    ,('jia','D')
                    ,('yong','O')
                    ,('yong','D');
SELECT * FROM role_tab;
SELECT person
	  ,CASE WHEN COUNT(*)=2 THEN 'B'
            ELSE MIN(role) END  role
WHERE role IN ('D', 'O')
FROM
	role_tab
GROUP BY person

上面的CASE WHEN 条件也可以写成

CASE WHEN MIN(role)<>MAX(role) THEN 'B' ELSE role END

神奇的解法
POSITION() 返回要查询的字符串在被查询字符串里第一次出现的位置
SUBSTRING ( expression, start, length )
POSITION() 将 D-1 O-2 然后 将每个人的表示相加
然后SUBSTRING函数D-1 O-2 B-3

SELECT person
	  ,SUBSTRING('DOB' FROM SUM(POSITION(role IN 'DO')) FOR 1) role
FROM
	role_tab
WHERE role IN ('D', 'O')
GROUP BY person;

本文参考资料如下：
[1] SQL 权威指南
[2] SQL 进阶教程
[3] SQL解惑