在SQL语句中,CASE表达式是非常重要的函数,在区分不同情况时常使用。
本文介绍CASE表达式的基本语句,语句的基本执行逻辑及使用场景。在使用场景中将以实例展示CASE表达式的用法。
目录
1.基本语句
2.语句基本执行逻辑
3.使用场景
3.1根据条件进行分类
3.2分类统计
3.3与GROUP BY的不同
3.4和GROUP BY 组合使用
1.基本语句
CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
ELSE <表达式>
END
在<求值表达式>中,会采用“列=值”的写法,表达式中也可以使用<>, LIKE,BETWEEN AND,等谓词。
在<求值表达式>中,可以使用AND或者OR写多个条件。
基本语句示例:
CASE WHEN sex='1' THEN '男'
WHEN sex='2' THEN '女'
ELSE '其他'
END
2.语句基本执行逻辑
对于数据表中的每一条记录,CASE表达式会从第一个WHEN开始执行判断,如果返回结果为真(TRUE),那么就返回THEN中的表达式。CASE表达式的执行结束。
如果第一个WHEN执行结果不为真,那么就继续执行下一个WHEN,直到有一个为真,返回THEN中的表达式。CASE表达式的执行结束。
如果所有的WHEN都不为真,那么返回ELSE表达式的结果。执行结束。
CASE语句在执行时,最终会返回一个值,即使写了几十行WHEN THEN,但是CASE表达式执行一次之后,也只会返回简单的值,比如上个示例中的’女‘、’男‘或者’其他‘。
3.使用场景
预先设定grade表,表中是一个班里学生的数学期末考试成绩:
id | name | score | sex |
001 | 张三 | 70 | 0 |
002 | 李四 | 50 | 0 |
003 | 王武 | 90 | 1 |
3.1根据条件进行分类
题目1:成绩表(grade)中,大于等于60分为及格,小于60分不不及格,查询学生的及格和不及格情况。
SELECT
name, score,
(CASE WHEN score < 60 THEN '不及格'
WHEN score >= 60 THEN '及格'
ELSE '异常'
END) AS remark
FROM
grade;
语句提示:
在SELECT子句中使用CASE表达式,表中的字段列可以和CASE表达式并存。
一般会给CASE表达式的结果设置别名(AS XXX)
ELSE子句如果不写,会默认为ELSE NULL
注意不要忽略END
返回结果:
name | score | remark |
张三 | 70 | 及格 |
李四 | 50 | 不及格 |
王武 | 90 | 及格 |
3.2分类统计
题目2:统计及格和不及格的人数
SELECT
SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END) AS sum_pass,
SUM(CASE WHEN score<60 THEN 1 ELSE 0 END) AS sum_fail
FROM grade;
语句解析:
在CASE表达式中,对于成绩及格(大于等于60)的学生,赋值为1,不及格的赋值为0,再利用SUM函数统计,得出的结果即是成绩及格学生数。
同理,对于成绩不及格的学生,赋值为1,对于成绩及格的学生,赋值为0,利用sum函数统计,得出的结果即为成绩不合格的学生数。
返回结果:
sum_pass | sum_fail |
2 | 1 |
题目3:统计男生的数量及男生及格的人数
SELECT
SUM(CASE WHEN sex='0' THEN 1 ELSE 0 END) AS sum_male,
SUM(CASE WHEN sex='0'AND score>=60 THEN 1 ELSE 0 END) AS sum_pass_male
FROM grade;
语句解析:
利用CASE表达式和SUM函数的结合。
SELECT子句中,设置如果性别为男,那么赋值1,否则赋值0,再进行SUM加和,求出男生的数量;
设置性别为男且分数及格,赋值为1,否则赋值0,再进行SUM加和,求出及格男生的数量。
在WHEN子句中可以跟多个条件,用AND或OR进行连接。
返回结果:
sum_male | sum_pass_male |
2 | 1 |
3.3与GROUP BY的不同
GROUP BY 是根据数据中,已有数据列进行分组,分组后可以进行统计。
CASE表达式可以根据已有数据判断分组(比如根据具体的考试成绩分为及格和不及格两种情况),然后再利用函数进行统计。
3.4和GROUP BY 组合使用
现有成绩表如下:
id | name | score | sex | class |
001 | 张三 | 70 | 0 | 高一班 |
002 | 李四 | 50 | 0 | 高二班 |
003 | 王武 | 90 | 1 | 高三班 |
004 | 张六 | 76 | 1 | 高一班 |
005 | 李七 | 52 | 0 | 高二班 |
006 | 王就 | 99 | 1 | 高一班 |
004 | 汤天 | 64 | 0 | 高三班 |
题目4:统计不同班级中,及格和不及格的人数
SELECT class,
SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END) AS sum_pass,
SUM(CASE WHEN Score<60 THEN 1 ELSE 0 END) AS sum_fail
FROM grade
GROUP BY class;
语句解析:
题目中需进行两次分组或分类,分别是“班级”和“及格/不及格”。可通过GROUP BY 将不同班级的数据分组,然后对每个班级中“及格/不及格”对人数进行统计。
在GROUP BY 子句中完成班级分组。
在SELECT子句中,及格的被赋值为1,不及格赋值为0,然后SUM函数加和,求出及格人数。同理,不及格赋值为1,及格赋值为0,然后SUM函数加和,求出不及格人数。
注意,由于在GROUP BY 中已经进行班级分组,SELECT语句执行时,是分别对每个班级的数据进行统计。(GROUP BY 的执行顺序先于 SELECT)
执行结果:
class | sum_pass | sum_fail |
高一班 | 3 | 0 |
高二班 | 0 | 2 |
高三班 | 2 | 0 |
由于是在SELECT子句中使用CASE表达式 ,每个CASE表达式的结果独立成列。
更多推荐
SQL基础系列(七)——CASE表达式
发布评论