MYSQL-CASE WHEN语句使用

CASE WHEN使用

数据库准备:

// 创建数据库(schema):
CREATE DATABASE school;
// 创建数据表
CREATE TABLE `fraction` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_no` int(11) DEFAULT NULL,
  `course` char(10) DEFAULT NULL,
  `fraction` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
// 插入数据
INSERT INTO fraction (student_no, course, fraction) values (101, '语文', 100),(102, '语文', 90),(103, '语文', 88),(101, '数学', 99), (102, '数学', 67),(103,'数学',99),(101, '英语', 60),(102, '英语', 69),(103,'英语', 53);

fraction 表数据如下:

id	student_no	course	fraction
1	101	语文	100
2	102	语文	90
3	103	语文	88
4	101	数学	99
5	102	数学	67
6	103	数学	99
7	101	英语	60
8	102	英语	69
9	103	英语	53

有时我们需要使每行学号的所有学科成绩显示在同一行, 可以使用CASE WHEN来实现这个效果。

CASE WHEN 语句用法:

CASE WHEN 表达式
    THEN 输出
WHEN 表达式
    THEN 输出
ELSE 
     输出
END

• CASE WHEN

SELECT  student_no, (CASE WHEN course = '语文' THEN fraction ELSE NULL END) AS CHINESE, (CASE WHEN course = '数学' THEN fraction ELSE NULL END) AS MATH, (CASE WHEN course = '英语' THEN fraction ELSE NULL END) AS english FROM fraction;

执行结果如下:

student_no	CHINESE	MATH	english
101	100	NULL	NULL
102	90	NULL	NULL
103	88	NULL	NULL
101	NULL	99	NULL
102	NULL	67	NULL
103	NULL	99	NULL
101	NULL	NULL	60
102	NULL	NULL	69
103	NULL	NULL	53

(用 CASE WHEN 语句分别对代表各个学科的"别名列"进行数据填充, 别名所代表的学科取对应学科所在行的fraction字段列的值, 其余行填充为NULL。)

• MAX(CASE WHEN) + GROUP BY

SELECT  student_no, MAX(CASE WHEN course = '语文' THEN fraction ELSE NULL END) AS CHINESE, MAX(CASE WHEN course = '数学' THEN fraction ELSE NULL END) AS MATH, MAX(CASE WHEN course = '英语' THEN fraction ELSE NULL END) AS english FROM fraction GROUP BY student_no;

执行结果如下:

student_no	CHINESE	MATH	english
101	100	99	60
102	90	67	69
103	88	99	53

(以student_no进行分组, 并对student_no值相同的行保留fraction值最大的一行。先分组, 后取大,再填充 ¹ )

---

1. SQL语句中GROUP BY的执行顺序优先于SELSECT. ↩︎