SQL 【Case When】 用法

1、文法

簡單Case函數

SELECT
	CASE student.student_sex
WHEN '男' THEN
	'0'
WHEN '女' THEN
	'1'
ELSE
	'2'
END AS sex
FROM
	student
           

case搜尋函數

SELECT
	CASE 
WHEN score.score_num >= 80 THEN
	'優秀'
WHEN score.score_num >= 60 AND score.score_num < 80 THEN
	'良好'
ELSE
	'合格'
END AS '成績'
FROM
	score
           

2、用處

(1) 已知資料按照另外一種方式分組

成績分等級、國家人口統計洲人口

(2) 分組統計

比如有如下表

country	sex	population
A	1	100
A	2	200
B	1	200
B	2	150

需要轉化如下結構

country	男	女
A	100	200
B	200	150

查詢sql：

SELECT
	country,
	sum(
		CASE
		WHEN sex = '1' THEN
			population
		ELSE
			0
		END
	) AS '男',
	sum(
		CASE
		WHEN sex = '2' THEN
			population
		ELSE
			0
		END
	) AS '女'
FROM
	country
GROUP BY
	country
	
           

參考

https://www.cnblogs.com/phpliu/archive/2010/05/20/1739990.html