1. 概述 1.1. SQL-92標準裡加入的最有用的特性 1.2. 寫法 1.2.1. 簡單CASE表達式 CASE sex WHEN '1' THEN ’男’ WHEN '2' THEN ’女’ ELSE ’其他’ END 1.2.1.1. 寫法簡單，但能實現的事情比較有限 1.2.2. 搜索C ...
1. 概述
1.1. SQL-92標準裡加入的最有用的特性
1.2. 寫法
-
1.2.1. 簡單CASE表達式
CASE sex WHEN '1' THEN ’男’ WHEN '2' THEN ’女’ ELSE ’其他’ END- 1.2.1.1. 寫法簡單，但能實現的事情比較有限
-
1.2.2. 搜索CASE表達式
CASE WHEN sex ='1'THEN’男’ WHEN sex ='2'THEN’女’ ELSE ’其他’ END-
1.2.2.1. 簡單CASE表達式能寫的條件，搜索CASE表達式也能寫
-
1.2.2.2. 可以使用BETWEEN、LIKE和＜、＞等便利的謂片語合，以及能嵌套子查詢的IN和EXISTS謂詞
-
1.2.2.3. CASE表達式用在SELECT子句里時，既可以寫在聚合函數內部，也可以寫在聚合函數外部
1.2.2.3.1. CASE表達式在執行時會被判定為一個固定值，因此它可以寫在聚合函數內部
1.2.2.3.2. 因為它是表達式，所以還可以寫在SELECE子句、GROUP BY子句、WHERE子句、ORDER BY子句里
-
1.2.2.4. 是支撐SQL聲明式編程的根基之一，也是靈活運用SQL時不可或缺的基礎技能
1.2.2.4.1. 在能寫列名和常量的地方，通常都可以寫CASE表達式
-
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. 示例
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. 用CHECK約束定義多個列的條件關係
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. 在UPDATE語句里進行條件分支
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. 表之間的數據匹配
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. 在CASE表達式中使用聚合函數
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;
