SQL进阶：一、CASE表达式

• 作为学习笔记存在

一、CASE表达式

-- 有两种表达式:
-- 表达式1
CASE gender 
	WHEN '1' THEN '男'
	WHEN '2' THEN '女'
	ELSE '未知性别' 
END AS 性别

-- 表达式2
CASE 
	WHEN gender = '1' THEN '男'
	WHEN gender = '2' THEN '女'
	ELSE '未知性别' 
END AS 性别

在CASE表达式里，可以使用BETEEN、LIKE和<，> ，IN等便利的谓词组合。

1.1 可以配合IN使用

-- 示例
CASE 
	WHEN city_name IN ('商丘','郑州') THEN '河南'
	WHEN city_name IN ('潍坊','淄博') THEN	'山东'
	ELSE '其它'
END

1.2 将已有编号方式转换为新的方式并统计

SELECT 
 CASE 
	 WHEN city_name IN ('郑州市','开封市','开封市','新乡市','周口市','商丘市') THEN '河南省' 
	 WHEN city_name IN ('南京市','无锡市','苏州市','常州市','南通市','扬州市') THEN '江苏省' 
	 ELSE '其他省份'
 END AS provinces ,
 sum(population) AS '总人口'
FROM poptbl p 
GROUP BY provinces;

1.3 将数值按照适当的级别进行分类统计

-- 按照人口数量等级查询市区个数。

SELECT 
	CASE 
		WHEN population < 100 THEN '低于100' 
		WHEN population >= 100 AND population < 200 THEN '大于100，小于200' 
		WHEN population >= 200 AND population < 300 THEN '大于200，小于300' 
		WHEN population >= 300  THEN '大于300' 
	END AS pop_class,
	count(*) 市区数量 
FROM poptbl p GROUP BY pop_class;

1.4 行转换列

poptbl2 数据：

ID	city_name	gender	population
2	周口市	1	150
3	周口市	2	110
4	商丘市	1	230
5	商丘市	2	220
6	南京市	1	90
7	南京市	2	190
8	无锡市	1	130
9	无锡市	2	150
10	常州市	1	90
11	常州市	2	190
12	苏州市	1	320
13	苏州市	2	310

-- 行转换列
SELECT
	city_name, 
	SUM(CASE WHEN gender = '1' THEN population ELSE	0 END) AS 男,
	SUM(CASE WHEN gender = '2' THEN population ELSE 0 END) AS 女
FROM
	poptbl2 p
GROUP BY
	city_name ;

转换后的数据：

city_name	男	女
周口市	150	110
商丘市	230	220
南京市	90	190
无锡市	130	150
常州市	90	190
苏州市	320	310

• 转换行列，在表头里加入汇总和再揭

SELECT 
 CASE 
 	WHEN gender = '1' THEN '男'
 	WHEN gender = '2' THEN '女'
 	ELSE '未知性别'
 END AS 性别,
 sum(population) AS 汇总,
 sum(CASE WHEN city_name = '周口市' THEN population ELSE 0 END ) AS '周口市',
 sum(CASE WHEN city_name = '商丘市' THEN population ELSE 0 END ) AS '商丘市',
 sum(CASE WHEN city_name = '南京市' THEN population ELSE 0 END ) AS '南京市',
 sum(CASE WHEN city_name = '无锡市' THEN population ELSE 0 END ) AS '无锡市',
 sum(CASE WHEN city_name = '常州市' THEN population ELSE 0 END ) AS '常州市',
 sum(CASE WHEN city_name = '苏州市' THEN population ELSE 0 END ) AS '苏州市',
 sum(CASE WHEN city_name IN ('周口市','商丘市') THEN population ELSE 0 END) AS  '河南'
 FROM 
 poptbl2 p GROUP BY gender ;

查询数据展示：

性别	汇总	周口市	商丘市	南京市	无锡市	常州市	苏州市	河南
男	1010	150	230	90	130	90	320	380
女	1170	110	220	190	150	190	310	330

新手用WHERE子句进行条件分支，高手用SELECT子句进行条件分支

1.5 update 语句进行条件分支。使用CASE

原始数据：SELECT * FROM salaries s ;

ID	name	salary
1	张三	300000
2	李四	270000
3	小红	220000
4	小李	290000
5	小北	350000

需求：

1. 对当前工资为30万元以上的员工，降薪10%。
2. 对当前工资为25万元以上且不满28万元的员工，加薪20%。

UPDATE salaries SET salary = 
CASE
	WHEN salary >= 300000 THEN salary * 0.9
	WHEN salary >= 250000 AND salary < 280000 THEN salary * 1.2
	ELSE salary  -- 此处别忘写，如果不符合调薪的条件保持原工资，不写会改为null
END;

行sql后的数据：

ID	name	salary
1	张三	270000
2	李四	324000
3	小红	220000
4	小李	290000
5	小北	315000

1.6 CASE使用IN、EXISTS谓词

coursemaster原始数据：

course_id	course_name
1	马克思主义
2	计算机科学与技术
3	物理学
4	生物学

opencourses原始数据：

month	course_id
202305	1
202305	3
202305	4
202306	4
202307	2
202307	4

1. 把每个学科在5、6、7月份是否开课列出来。开课为O，不开课为X。

   -- 使用IN谓词
   SELECT course_name AS 课程名称,
   CASE 
   	WHEN course_id IN (SELECT course_id FROM opencourses o WHERE months = '202305') THEN 'O'
   	ELSE 'X'
   END AS '5月份',
   CASE 
   	WHEN course_id IN (SELECT course_id FROM opencourses o WHERE months = '202306') THEN 'O'
   	ELSE 'X'
   END AS '6月份',
   CASE 
   	WHEN course_id IN (SELECT course_id FROM opencourses o WHERE months = '202307') THEN 'O'
   	ELSE 'X'
   END AS '7月份'
   FROM coursemaster c ;
   
   -- 使用EXISTS谓词
   SELECT course_name AS '课程名称',
   CASE 
   	WHEN  EXISTS (SELECT course_id FROM opencourses o WHERE months = '202305' AND o.course_id = c.course_id) THEN 'O'
   	ELSE 'X'
   END AS '5月份',
   CASE 
   	WHEN  EXISTS (SELECT course_id FROM opencourses o WHERE months = '202306'  AND o.course_id = c.course_id) THEN 'O'
   	ELSE 'X'
   END AS '6月份',
   CASE 
   	WHEN  EXISTS (SELECT course_id FROM opencourses o WHERE months = '202307'  AND o.course_id = c.course_id) THEN 'O'
   	ELSE 'X'
   END AS '7月份'
   FROM coursemaster c ;
   

   查询数据展示：

   课程名称	5月份	6月份	7月份
   马克思主义	O	X	X
   计算机科学与技术	X	X	O
   物理学	O	X	X
   生物学	O	O	O

1.7 在CASE表达式中使用聚合函数

studentClub 原数据。 main_club_flg设置Y或者N来表明哪一个社团是他的主社团标志。对于只加入了一个社团的学生，将其“主社团标志”设置为N。

std_id	club_id	club_name	main_club_flg
2023001	1	棒球	Y
2023001	2	管弦乐	N
2023002	2	管弦乐	N
2023002	3	羽毛球	Y
2023002	4	足球	N
2023003	4	足球	N
2023004	5	游泳	N
2023005	6	围棋	N

1. 获取之加入了一个社团的学生的社团ID。
2. 获取加入了多个社团的学生的主社团ID。

-- 经分析，都是获取学生的主社团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
FROM studentclub s GROUP BY std_id ;

这句话要理解：如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中，也就是说查出来的列必须在group by后面出现否则就会报错，或者这个字段出现在聚合函数里面。

查询出来的数据：

std_id	main_club
2023001	1
2023002	3
2023003	4
2023004	5
2023005	6