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