例子:
-- 使用 搜索CASE表达式 的情况【推荐】 SELECT product_name, CASE WHEN product_type = '衣服' THEN 'A :' || product_type WHEN product_type = '办公用品' THEN 'B :' || product_type WHEN product_type = '厨房用具' THEN 'C :' || product_type ELSE NULL END AS abc_product_type FROM Product; -- 使用 简单CASE表达式 的情况 SELECT product_name, CASE product_type WHEN '衣服' THEN 'A :' || product_type WHEN '办公用品' THEN 'B :' || product_type WHEN '厨房用具' THEN 'C :' || product_type ELSE NULL END AS abc_product_type FROM Product;注意:
1、统一各分支返回的数据类型。例如用 CAST 函数。
2、ELSE 子句可以省略不写,这时会被默认为 ELSE NULL。但还是建议 养成写 ELSE 子句的习惯,减少失误。
3、记得写 END 。
4、WHEN NULL 错误,WHEN IS NULL正确。
案例 1、用一条 SQL 语句进行不同条件的统计统计不同县的男女比例(“县名”的列为:pref_name,“人口”的列为:population)
-- == old 写法: -- 男性人口 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; -- == new 写法: 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;新手用 WHERE 子句进行条件分支,高手用 SELECT 子句进行条件分支。
案例 2、在 CASE 表达式中使用聚合函数std_id ( 学号) 、club_id ( 社团ID) 、club_name ( 社团名) 、main_club_flg ( 主社团标志)
获取只加入了一个社团的学生的社团ID。
获取加入了多个社团的学生的主社团ID。
-- == old 写法: -- 条件1 :选择只加入了一个社团的学生 SELECT std_id, MAX(club_id) AS main_club FROM StudentClub GROUP BY std_id HAVING COUNT(*) = 1; -- 条件2 :选择加入了多个社团的学生 SELECT std_id, club_id AS main_club FROM StudentClub WHERE main_club_flg = 'Y' ; -- == new 写法: 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;新手用 HAVING 子句进行条件分支,高手用 SELECT 子句进行条件分支。
案例 3、用 CHECK 约束定义多个列的条件关系假设某公司规定 “女性员工的工资必须在 20 万日元以下”
CONSTRAINT check_salary CHECK ( CASE WHEN sex = '2' THEN CASE WHEN salary <= 200000 THEN 1 ELSE 0 END ELSE 1 END = 1 ) 案例 4、在 UPDATE 语句里进行条件分支,避免多次循环更新的出错例子1:多次循环更新
例如你要:
对当前工资为 30 万日元以上的员工,降薪 10%。
对当前工资为 25 万日元以上且不满 28 万日元的员工,加薪 20%。
-- 错误写法:(问题在于,第一次的 UPDATE 操作执行后,“当前工资”发生了变化,如果还用它当作第二次 UPDATE 的判定条件,结果就会出错。) UPDATE Salaries SET salary = salary * 0.9 WHERE salary >= 300000; UPDATE Salaries SET salary = salary * 1.2 WHERE salary >= 250000 AND salary < 280000; -- 正确写法: UPDATE Salaries SET salary = CASE WHEN salary >= 300000 THEN salary * 0.9 WHEN salary >= 250000 AND salary < 280000 THEN salary * 1.2 ELSE salary -- 注意这里的 `ELSE salary` 非常重要 END;例子2:两个值交换(替代传统的使用中间值的做法)
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'); 第 7 章 集合运算【重难点】面向对象语言以对象的方式来描述世界,而面向集合语言SQL 以集合的方式来描述世界。
1、集合运算符(以集合为单位)表的加法(并集) —— UNION (UNION ALL)
表的减法(差集) —— EXCEPT (EXCEPT ALL)
表的(交集) —— INTERSECT (INTERSECT ALL)
表的乘法、除法下面会提到。
上面运算符后加了 ALL 的表示算出结果后,不会除去重复记录。
加了 ALL 就不会为了除去重复行而发生排序,所以性能会有提升。
注意事项 ① —— 作为运算对象的记录的列数必须相同
注意事项 ② —— 作为运算对象的记录中列的类型必须一致
注意事项 ③ —— ORDER BY 子句只能在最最后使用一次