- 作为学习笔记存在
一、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 |
需求:
- 对当前工资为30万元以上的员工,降薪10%。
- 对当前工资为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 |
-
把每个学科在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 |
- 获取之加入了一个社团的学生的社团ID。
- 获取加入了多个社团的学生的主社团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 |