WHEN CASE 表达式
基础语法
- 简单 CASE 表达式
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
- 搜索CASE表达式
CASE
WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END
GROUP BY + WHEN CASE <=> group by district
用途:多行转一行 (统计数据)
SELECT
CASE pref_name
WHEN '德岛' THEN '四国'
WHEN '香川' THEN '四国'
WHEN '爱媛' THEN '四国'
WHEN '高知' THEN '四国'
WHEN '福冈' THEN '九州'
WHEN '佐贺' THEN '九州'
WHEN '长崎' THEN '九州'
ELSE '其他' END AS district,
SUM(population)
FROM PopTbl
GROUP BY CASE pref_name
WHEN '德岛' THEN '四国'
WHEN '香川' THEN '四国'
WHEN '爱媛' THEN '四国'
WHEN '高知' THEN '四国'
WHEN '福冈' THEN '九州'
WHEN '佐贺' THEN '九州'
WHEN '长崎' THEN '九州'
ELSE '其他' END;
// 等价
SELECT CASE
pref_name
WHEN '德岛' THEN
'四国'
WHEN '香川' THEN
'四国'
WHEN '爱媛' THEN
'四国'
WHEN '高知' THEN
'四国'
WHEN '福冈' THEN
'九州'
WHEN '佐贺' THEN
'九州'
WHEN '长崎' THEN
'九州'
ELSE '其他'
END AS district,
SUM(population)
FROM PopTbl
GROUP BY district
查询结果中进行统计
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
SELECT sex,
SUM(population) AS total,
SUM(CASE WHEN pref_name = '德岛' THEN population ELSE 0 END) AS col_1,
SUM(CASE WHEN pref_name = '香川' THEN population ELSE 0 END) AS col_2,
SUM(CASE WHEN pref_name = '爱媛' THEN population ELSE 0 END) AS col_3,
SUM(CASE WHEN pref_name = '高知' THEN population ELSE 0 END) AS col_4,
SUM(CASE WHEN pref_name IN ('德岛', '香川', '爱媛', '高知')
THEN population ELSE 0 END) AS zaijie
FROM PopTbl2
GROUP BY sex;
UPDATE + WHEN 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
两个字段相互调换位置
把表中两个字段对掉位置
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' );
多表转单表
-- 方法一
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;
-- 方法二
SELECT CM.course_name,
CASE WHEN EXISTS
(SELECT course_id FROM OpenCourses OC
WHERE month = 200706
AND CM.course_id = OC.course_id) THEN '○'
ELSE '×' END AS "6月",
CASE WHEN EXISTS
(SELECT course_id FROM OpenCourses OC
WHERE month = 200707
AND CM.course_id = OC.course_id) THEN '○'
ELSE '×' END AS "7月",
CASE WHEN EXISTS
(SELECT course_id FROM OpenCourses OC
WHERE month = 200708
AND CM.course_id = OC.course_id) THEN '○'
ELSE '×' END AS "8月"
FROM CourseMaster CM;
三列字段取最大值
-- 用 WHEN ... CASE 也可以
SELECT
g.key, IF((IF(x > y, x, y)) < z, z, (IF(x > y, x, y))) AS Greatests
FROM
greatests g
-- 最大值
SELECT
greatest(x,y,z)
FROM
greatests
将key按照指定顺序排序 B -> A -> C -> D
SELECT g.key,
CASE g.key
WHEN 'B' THEN 1
WHEN 'A' THEN 2
WHEN 'D' THEN 3
WHEN 'C' THEN 4
ELSE NULL END AS sort_col
FROM Greatests g
ORDER BY sort_col;
SELECT g.key
FROM Greatests g
ORDER BY CASE g.key
WHEN 'B' THEN 1
WHEN 'A' THEN 2
WHEN 'D' THEN 3
WHEN 'C' THEN 4
ELSE NULL END;