MySql 实用小技巧001 - CASE表达式(《SQL进阶教程》学习笔记)

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;