1.1. SQL-92标准里加入的最有用的特性
1.2. 写法
CASE sex
WHEN '1' THEN ’男’
WHEN '2' THEN ’女’
ELSE ’其他’ END
CASE WHEN sex ='1'THEN’男’
WHEN sex ='2'THEN’女’
ELSE ’其他’ END
1.3. 注意事项
-
1.3.1. 统一各分支返回的数据类型
- 1.3.1.1. 某个分支返回字符型，而其他分支返回数值型的写法是不正确的
-
1.3.2. 要注意条件的排他性
-
1.3.3. 不要忘了写END
-
1.3.4. 养成写ELSE子句的习惯
-
1.3.4.1. ELSE子句是可选的
-
1.3.4.2. 不写也不会出错
1.3.4.2.1. 不写可能会造成“语法没有错误，结果却不对”这种不易追查原因的麻烦
-
1.3.4.3. 即便是在结果可以为NULL的情况下
-
1.4. 必须在SELECT子句和GROUP BY子句这两处写一样的CASE表达式
1.5. GROUP BY子句使用的正是SELECT子句里定义的列的别称
-
1.5.1. 这种写法是违反标准SQL的规则的
-
1.5.1.1. 因为GROUP BY子句比SELECT语句先执行
-
1.5.1.2. 所以在GROUP BY子句中引用在SELECT子句里定义的别称是不被允许的
-
-
1.5.2. Oracle、DB2、SQL Server等数据库里采用这种写法时就会出错
-
1.5.3. 在PostgreSQL和MySQL中可以顺利执行
-
1.5.4. 不强烈推荐大家使用
2.1.
-- 男性人口
SELECT pref_name,
SUM(population)
FROM PopTbl2
WHERE sex ='1'
GROUP BY pref_name;
-- 女性人口
SELECT pref_name,
SUM(population)
FROM PopTbl2
WHERE sex ='2'
GROUP BY pref_name;
2.2.
SELECT pref_name,
--男性人口
SUM( CASE WHEN sex ='1'THEN population ELSE 0 END) AS cnt_m,
--女性人口
SUM( CASE WHEN sex ='2'THEN population ELSE 0 END) AS cnt_f
FROM PopTbl2
GROUP BY pref_name;
2.3. 能将SQL的查询结果转换为二维表的格式
- 2.3.1. 新手用WHERE子句进行条件分支，高手用SELECT子句进行条件分支
3.1.
CONSTRAINT check_salary CHECK
( CASE WHEN sex ='2'
THEN CASE WHEN salary ＜= 200000
THEN 1 ELSE 0 END
ELSE 1 END = 1 )
- 3.1.1. 使用蕴含式，男性也可以在这里工作
3.2.
CONSTRAINT check_salary CHECK
( sex ='2'AND salary ＜= 200000 )
- 3.2.1. 使用逻辑与，该公司将不能雇佣男性员工
4.1.
--条件1
UPDATE Salaries
SET salary = salary ＊ 0.9
WHERE salary ＞= 300000;
--条件2
UPDATE Salaries
SET salary = salary ＊ 1.2
WHERE salary ＞= 250000 AND salary ＜ 280000;
4.2. 用CASE表达式写正确的更新操作
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;
-
4.2.1. 最后一行的ELSE salary非常重要，必须写上
-
4.2.2. 因为如果没有它，条件1和条件2都不满足的员工的工资就会被更新成NULL
4.3.
--1．将a转换为中间值d
UPDATE SomeTable
SET p_key ='d'
WHERE p_key ='a';
--2．将b调换为a
UPDATE SomeTable
SET p_key ='a'
WHERE p_key ='b';
--3．将d调换为b
UPDATE SomeTable
SET p_key ='b'
WHERE p_key ='d';
4.4. 用CASE表达式调换主键值
UPDATE SomeTable
SET p_key = CASE WHEN p_key ='a'
THEN 'b'
WHEN p_key ='b'
THEN 'a'
ELSE p_key END
WHERE p_key IN ('a', 'b');
5.1. 表的匹配：使用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;
5.2. 表的匹配：使用EXISTS谓词
SELECT CM.course_name,
CASE WHEN EXISTS
(SELECT course_id FROM OpenCourses OC
WHERE month = 200706
AND OC.course_id = CM.course_id) THEN'○'
ELSE'×'END AS "6月",
CASE WHEN EXISTS
(SELECT course_id FROM OpenCourses OC
WHERE month = 200707
AND OC.course_id = CM.course_id) THEN'○'
ELSE'×'END AS "7月",
CASE WHEN EXISTS
(SELECT course_id FROM OpenCourses OC
WHERE month = 200708
AND OC.course_id = CM.course_id) THEN'○'
ELSE'×'END AS "8月"
FROM CourseMaster CM;
- 5.2.1. 从性能方面来说，EXISTS更好
6.1. 条件1：选择只加入了一个社团的学生
SELECT std_id, MAX(club_id) AS main_club
FROM StudentClub
GROUP BY std_id
HAVING COUNT(＊) = 1;
6.2. 条件2：选择加入了多个社团的学生
SELECT std_id, club_id AS main_club
FROM StudentClub
WHERE main_club_flg ='Y';
6.3.
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;