一、基本语法

-- 简单case表达式
case sex
	when '1' then '男'
	when '2' then '女'
else '其他' end

--搜索case表达式
case when sex = '1' then '男'
	 when sex = '2' then '女'
else '其他' end

注意：

• 统一各分支返回的数据类型；
• 不要忘了写end;
• 养成写else子句的习惯。

二、应用

1. 将已有编号转换为新的方式并统计

按地区分类统计：

SELECT
case
	when pref_name = '德岛' OR pref_name = '香川' OR pref_name = '爱媛' OR pref_name = '高知' then '四国'
	when pref_name = '福冈' OR pref_name = '佐贺' OR pref_name = '长崎' then  '九州' 
	ELSE  '其他'
END
AS area_name, sum(population)
FROM poptbl
GROUP BY 
case
	when pref_name = '德岛' OR pref_name = '香川' OR pref_name = '爱媛' OR pref_name = '高知' then '四国'
	when pref_name = '福冈' OR pref_name = '佐贺' OR pref_name = '长崎' then  '九州' 
	ELSE  '其他'
END;

按人口数量分类统计：

SELECT 
case 
	when population <= 100 then '01'
	when population <= 200 then '02'
	when population <= 300 then '03'
	when population <= 400 then '04'
	ELSE '10'
END
AS pop_class, COUNT(pref_name) AS cnt
FROM poptbl
GROUP BY pop_class;

2. 用一条SQL语句进行多条件统计

按性别和地区统计：

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

3. 在update语句里进行条件分支

更新工资

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')

4. 表之间的数据匹配

生成两张表的交叉表

使用IN谓词

SELECT course_name,
  CASE 
    WHEN course_id in 
    (SELECT course_id from opencourses WHERE `month` = 201806)
  THEN
      'O'
  ELSE
      'X'
  END AS '6月',
  CASE 
    WHEN course_id in 
    (SELECT course_id from opencourses WHERE `month` = 201807)
  THEN
      'O'
  ELSE
      'X'
  END AS '7月',
  CASE 
    WHEN course_id in 
    (SELECT course_id from opencourses WHERE `month` = 201808)
  THEN
      'O'
  ELSE
      'X'
  END AS '8月'
FROM coursemaster;

使用EXISTS谓词

SELECT CM.course_name,
  CASE 
    WHEN EXISTS 
    (
      SELECT course_id 
      from opencourses OC 
      WHERE `month` = 201806
      AND OC.course_id = CM.course_id
    )
  THEN
      'O'
  ELSE
      'X'
  END AS '6月',
  CASE 
    WHEN EXISTS 
    (
      SELECT course_id 
      from opencourses OC 
      WHERE `month` = 201807
      AND OC.course_id = CM.course_id
    )
  THEN
      'O'
  ELSE
      'X'
  END AS '7月',
  CASE 
    WHEN EXISTS 
    (
      SELECT course_id 
      from opencourses OC 
      WHERE `month` = 201808
      AND OC.course_id = CM.course_id
    )
  THEN
      'O'
  ELSE
      'X'
  END AS '8月'
FROM coursemaster CM;

5. 在case表达式中使用聚合函数

查询加入一个社团的学生和主社团

select std_id, max(club_id) as main_club
from studentclub
GROUP BY std_id
HAVING COUNT(std_id) = 1

查询加入多个社团的学生和主社团

select std_id, club_id as main_club
from studentclub
WHERE main_club_flg = 'Y'

使用CASE表达式

SELECT
  std_id,
CASE
    WHEN count( std_id ) = 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子句进行条件分支