case函数用于选择判断,只返回第一个符合条件的值,剩下的case部分将会被自动忽略。
有以下两种写法(本教程所用表以及数据见教程底部):
简单case函数
格式
CASE [col_name] WHEN [value1] THEN [result1]… ELSE [default] END
示例
#简单case函数
SELECT
( CASE stu_no WHEN '001' THEN '张三' WHEN '002' THEN '李四' WHEN '003' THEN '王五' ELSE '无' END ) '学生姓名',
( CASE course_name WHEN 'Chinese' THEN '语文' WHEN 'Math' THEN '数学' WHEN 'English' THEN '英语' ELSE '无' END ) '课程名称',
score
FROM
grade;
结果
case搜索函数
格式
CASE WHEN [expr] THEN [result1]… ELSE [default] END
示例
#case搜索函数
SELECT
#简单case函数示例
( CASE stu_no WHEN '001' THEN '张三' WHEN '002' THEN '李四' WHEN '003' THEN '王五' ELSE '无' END ) '学生姓名',
( CASE course_name WHEN 'Chinese' THEN '语文' WHEN 'Math' THEN '数学' WHEN 'English' THEN '英语' ELSE '无' END ) '课程名称',
#case搜索函数示例
( CASE WHEN score > 85 THEN '优' WHEN score BETWEEN 60 AND 85 THEN '良' WHEN score < 60 THEN '差' ELSE '无' END ) '成绩'
FROM
grade;
结果
区别
简单case函数对于固定值进行选择判断,枚举这个字段所有可能的值,比较简单,不适合复杂的问题
case搜索函数可以写判断式,功能要更强大一些,建议使用case搜索函数
扩展:case when强大的行转列功能
需求:查询学生每科成绩
普通实现
#实现一 连表查(最常见) 产生了学号以及学生姓名数据的冗余
SELECT
s.stu_no,
s.stu_name,
g.course_name,
g.score
FROM
student s
LEFT JOIN grade g ON s.stu_no = g.stu_no;
结果:
Case when行转列实现
#实现二 行转列(最简洁) 利用case when强大的行转列功能
SELECT
s.stu_no,
s.stu_name,
sum( CASE g.course_name WHEN 'Chinese' THEN g.score ELSE 0 END ) AS '语文',
sum( CASE g.course_name WHEN 'Math' THEN g.score ELSE 0 END ) AS '数学',
sum( CASE g.course_name WHEN 'English' THEN g.score ELSE 0 END ) AS '英语'
FROM
student s
LEFT JOIN grade g ON s.stu_no = g.stu_no
GROUP BY s.stu_no;
结果:
注意:
聚合函数sum()筛选出学生的唯一一条课程成绩
GROUP BY分组显示每一位学生课程成绩
多个case when一起使用可以将行数据转为列数据,在使用行转列的时候要注意每个case后边都要跟着一个end来作为结束,否则将会报错。
测试数据
#当前存在两张数据表:学生表、成绩表,数据表关系是为,一对多;
#新建表
#学生表
CREATE TABLE `student` (
`stu_no` VARCHAR (16) NOT NULL COMMENT '学号',
`stu_name` VARCHAR (20) NOT NULL COMMENT '学生姓名',
PRIMARY KEY (`stu_no`)
) COMMENT = '学生表' ENGINE = INNODB;
#成绩表
CREATE TABLE `grade` (
`stu_no` VARCHAR (16) NOT NULL COMMENT '学号',
`course_name` VARCHAR (20) NOT NULL COMMENT '课程名称',
`score` FLOAT NULL DEFAULT NULL COMMENT '分数',
PRIMARY KEY (`stu_no`,`course_name`)
) COMMENT = '成绩表' ENGINE = INNODB;
#插入数据
#学生表数据
INSERT INTO student (stu_no, stu_name)
VALUES
('001', '张三'),
('002', '李四'),
('003', '王五');
#成绩表数据
INSERT INTO grade (stu_no, course_name, score)
VALUES
('001', 'Chinese', 73),('002', 'Chinese', 85),('003', 'Chinese', 99),
('001', 'Math', 88),('002', 'Math', 77),('003', 'Math', 82),
('001', 'English', 93),('002', 'English', 95),('003', 'English', 59);