SQL Case when技巧 分阶段统计 二维表 分组汇总 等

Case when技巧

1. 概述

CASE表达式有简单CASE表达式（simple case expression）和搜索CASE表达式（searched
case expression）两种写法如下：

基本上采用搜索CASE表达式的写法
与DECODE函数等相比，CASE表达式的一大优势在于能够判断表达式。也就是说，在
CASE表达式里，我们可以使用BETWEEN、LIKE和<、>等便利的谓词组合，以及能嵌套
子查询的IN和EXISTS谓词。

2. 注意事项

注意事项1：统一各分支返回的数据类型
注意事项2：不要忘了写END
注意事项3：养成写ELSE子句的习惯

3. 示例

3.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;

3.2分阶段统计

统计结果：

SELECT CASE
 WHEN [年龄] < 20 THEN
 '<20'
 WHEN 年龄 > 20 THEN
 '>20'
 ELSE
 '其他'
 END ,
 COUNT(姓名)
FROM dbo.hr_data
GROUP BY CASE
 WHEN 年龄 < 20 THEN
 '<20'
 WHEN 年龄 > 20 THEN
 '>20'
 ELSE
 '其他'
 END;

3.3 二维表统计

统计结果：

SELECT [用工关系],
 SUM( CASE
 WHEN [性别] = '男' THEN
 1
 ELSE
 0
 END
 ) AS '男人数量',
 SUM( CASE
 WHEN [性别] = '女' THEN
 1
 ELSE
 0
 END
 ) AS '女人数量'
FROM hr_data
GROUP BY [用工关系];

3.4定义蕴含条件的约束

CONSTRAINT check_salary CHECK
(CASE WHEN sex ='2'
			THEN CASE WHEN salary <= 200000
					THEN 1 ELSE 0 END
			ELSE 1 END = 1 )

说明：在性别为女时，拒绝薪资高于200000的员工

3.5 case 中查询另一张表

SELECT course_name,
 CASE
	 WHEN EXISTS
	 (
	 SELECT dbo.openCourse.id
	 FROM dbo.openCourse
	 WHERE dbo.openCourse.mon = '一月'
	 AND dbo.openCourse.courseId = dbo.course.id
	 ) THEN
	 'O'
	 ELSE
	 'x'
	 END AS '一月',
 CASE
	 WHEN EXISTS
	 (
	 SELECT dbo.openCourse.id
	 FROM dbo.openCourse
	 WHERE dbo.openCourse.mon = '二月'
	 AND dbo.openCourse.courseId = dbo.course.id
	 ) THEN
	 'O'
	 ELSE
	 'x'
	 END AS '二月',
 CASE
	 WHEN EXISTS
	 (
	 SELECT dbo.openCourse.id
	 FROM dbo.openCourse
	 WHERE dbo.openCourse.mon = '三月'
	 AND dbo.openCourse.courseId = dbo.course.id
	 ) THEN
	 'O'
	 ELSE
	 'x'
	 END AS '三月'
FROM dbo.course;

说明：利用case 联查了相关表 没有用left join
无论使用IN还是EXISTS，得到的结果是一样的，但从性能方面来说，EXISTS更
好。通过EXISTS进行的子查询能够用到“month, course_id”这样的主键索引

3.6 case 中使用聚合函数

需求：找出主社团，只有一个社团的，社团id就是唯一社团的id，加入多个社团
的看主社团标记
源数据：

说明：有的学生只加入了某一个社团（如学号为300、400、500的学
生）。对于加入了多个社团的学生，我们通过将其“主社团标志”列设置为Y
或者N来表明哪一个社团是他的主社团；对于只加入了一个社团的学生，
我们将其“主社团标志”列设置为N。

统计结果：

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;