目录
case表达式介绍
1. case表达式优点：
CASE表达式不依赖于具体的数据库技术，可以提高SQL代码的可移植性
2. 书写case表达式时的注意事项
1）注意事项1：统一各分支返回的数据类型要一致
2）注意事项2：不要忘了写END
3）注意事项3：养成写ELSE子句的习惯
练习
1、将已有编号方式转换为另外一种便于分析的方式并进行统计
题目
1) 将地区名转成洲名，按州汇总人口
2) 将地区名转成人口等级，按人口等级汇总人口
程序实现
CREATE DATABASE test;
use test;
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);
/*查看数据*/
select *
from PopTbl;
/*
题目：按州汇总人口
解题步骤：
1、使用case表达式将县名替换为州名
2、使用group by语句进行分组汇总
*/
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 pop_num
FROM PopTbl
GROUP BY CASE pref_name
WHEN '德岛' THEN ' 四国'
WHEN '香川' THEN ' 四国'
WHEN '爱媛' THEN ' 四国'
WHEN '高知' THEN ' 四国'
WHEN '福冈' THEN ' 九州'
WHEN '佐贺' THEN ' 九州'
WHEN '长崎' THEN ' 九州'
Else '其他' END;
ORDER BY pop_num
/*
按不同人口等级汇总人口
解题步骤：
1、使用case表达式将县名替换为人口量级
2、使用group by语句进行分组汇总
*/
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 '其他'
END AS pop_class,
SUM(population) AS pop_num
FROM PopTbl
GROUP BY 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 '其他'
END
ORDER BY pop_class小节
这类题目特点是：将某一列按照不同条件划分到不同类别，然后分组统计
2. 进行不同条件的统计
题目
往存储各县人口数量的表PopTbl2里添加上“性别”列，然后求按性别、县名汇总的人数
程序实现
/* 用一条SQL语句进行不同条件的统计 */
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);
SELECT pref_name,
SUM(CASE WHEN sex=1 THEN population ELSE 0 END) AS '男',
SUM(CASE WHEN sex=2 THEN population ELSE 0 END) AS '女'
FROM PopTbl2
GROUP BY pref_name
ORDER BY pref_name小结
这类题的特点是：根据某一列不同条件、类别（eg：不同性别），对另一列数据进行统计，从一维表转为二维表。
3.用CHECK约束定义多个列的条件关系
问题：
规定“女性员工的工资必须在20万日元以下”
代码
ALTER TABLE Salary ADD CONSTRANT
CHECK(CASE WHEN sex='2' THEN
CASE WHEN salary<=200000 THEN 1 ELSE 0 END
ELSE 0 END = 1)
4. 在UPDATE语句里进行条件分支
问题
假设现在需要根据以下条件对该表的数据进行更新。1．对当前工资为30万日元以上的员工，降薪10%。2．对当前工资为25万日元以上且不满28万日元的员工，加薪20%。
程序实现
/* 员工工资信息表 */
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);
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;小节
这类题的特点：以某数值型的列的当前值为判断对象，将其更新成别的值
用CASE表达式的条件分支进行的更新操作是一气呵成的，可以避免出现主键重复所导致的错误
5. 表之间的数据匹配
在CASE表达式里，我们可以使用BETWEEN、LIKE和<、>等便利的谓词组合，以及能嵌套子查询的IN和EXISTS谓词
问题
用这两张表来生成交叉表
程序实现
/* 表之间的数据匹配 */
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);
select CM.course_name,
CASE WHEN EXISTS( SELECT course_id FROM OpenCourses AS OC WHERE month = 200706 AND OC.course_id=CM.course_id) THEN'○' ELSE '×' END AS '六月',
CASE WHEN EXISTS( SELECT course_id FROM OpenCourses AS OC WHERE month = 200707 AND OC.course_id=CM.course_id) THEN'○' ELSE '×' END AS '七月',
CASE WHEN EXISTS( SELECT course_id FROM OpenCourses AS OC WHERE month = 200708 AND OC.course_id=CM.course_id) THEN'○' ELSE '×' END AS '八月'
FROM CourseMaster CM小节
这道题和上方练习二的思想差不多，练习二是在一张表中，根据某一列不同条件、类别（eg：不同性别），对另一列数据进行统计。而这道题是通过将两张表联结（联结方式是子查询），根据其中一列数据进行分条件统计
6.在CASE表达式中使用聚合函数
问题
主社团标志”列设置为Y或者N来表明哪一个社团是他的主社团；对于只加入了一个社团的学生，我们将其“主社团标志”列设置为N。接下来，我们按照下面的条件查询这张表里的数据。
1．获取只加入了一个社团的学生的社团ID。
2．获取加入了多个社团的学生的主社团ID。
程序实现
/* 在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');
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;小节
1.CASE表达式用在SELECT子句里时，既可以写在聚合函数内部（例如练习二），也可以写在聚合函数外部（本题）
2.使用MAX的原因：
使用聚合函数时，select语句中只能出现：聚合键（group by指定的列名）、聚合函数、常数
参考书籍
sql进阶教程
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
