专栏首页测试基础【Mysql-3】条件判断函数-CASE WHEN、IF、IFNULL详解

【Mysql-3】条件判断函数-CASE WHEN、IF、IFNULL详解

前言

在众多SQL中,统计型SQL绝对是让人头疼的一类,之所以如此,是因为这种SQL中必然有大量的判读对比。而条件判断函数就是应对这类需求的利器。本文重点总结CASE WHENIFIFNULL三种函数。

1 CASE WHEN

Case when语句能在SQL语句中织入判断逻辑,类似于Java中的if else语句。

CASE WHEN语句分为简单函数和条件表达式。

1、简单函数

CASE 字段 WHEN 预期值 THEN 结果1 ELSE 结果2 END

如果字段值等于预期值,则返回结果1,否则返回结果2。

下面通过一个简单的示例来看一下具体用法。

表score:

场景:在score表中,sex为1表示男性,sex=0表示女性,查询时转换成汉字显示。

SQL语句

SELECT name,(CASE sex WHEN 0 THEN '女' ELSE '男' END) sex FROM score

结果

2、条件表达式

CASE的简单函数使用简便,但无法应对较为复杂的场景,这就需要用到条件表达式了,其语法结构如下:

CASE 
	WHEN condition THEN result1  ELSE result2
END

解释一下,语句中的condition是条件判断,如果该判断结果为true,那么CASE语句将返回result,否则返回result2,如果没有ELSE,则返回null。CASE与END之间可以有多个WHEN…THEN…ELSE语句。END表示CASE语句结束。

场景:score 大于等于90为优秀,80-90为良好,60-80为及格,小于60为不及格,用SQL语句统计出每个学生的成绩级别。

SQL:

SELECT name,score,(CASE 
	WHEN score>=90 THEN '优秀' 
	WHEN score>=80 THEN '良好' 
	WHEN score>=60 THEN '及格' 
	ELSE '不及格' END) level 
FROM score

结果

3、综合使用

CASE WHEN 和 聚合函数综合使用,能实现更加复杂的统计功能。

先看第1个场景

在下表score(sex=1为男,sex=0为女)中,统计有多少个男生和女生以及男女生及格的各有多少个。

SQL:

SELECT 
	SUM(CASE WHEN sex=0 THEN 1 ELSE 0 END) AS 女生人数,
	SUM(CASE WHEN sex=1 THEN 1 ELSE 0 END) AS 男生人数,
	SUM(CASE WHEN score>=60 AND sex=0 THEN 1 ELSE 0 END) 男生及格人数,
	SUM(CASE WHEN score>=60 AND sex=1 THEN 1 ELSE 0 END) 女生及格人数
FROM score;

结果

再看第2个场景

将上面的score表转换为下面形式:

SQL:

SELECT 
	name,
	MAX(CASE course WHEN '语文' THEN score ELSE 0 END) AS '语文',
	max(CASE course WHEN '数学' THEN score ELSE 0 END) AS '数学',
	max(CASE course WHEN '英语' THEN score ELSE 0 END) AS '英语',
	AVG(score) AS '平均成绩'
FROM score GROUP BY name;

结果如下:

2 IF

IF函数也能通过判断条件来返回特定值,它的语法如下:

IF(expr,result_true,result_false)

expr是一个条件表达式,如果结果为true,则返回result_true,否则返回result_false。

用一个示例演示,还是表score:

使用IF函数:

SELECT name,IF(sex=1,'男','女')sex FROM students;

可以看出,在一些场景中,IF函数和CASE WHEN是有同样效果的,前者相对简单,后者能应对更复杂的判断。

另外,IF函数还可以和聚合函数结合,例如查询班级男生女生分别有多少人:

SELECT COUNT(IF(sex=1,1,NULL)) 男生人数,COUNT(IF(sex=0,1,NULL))女生人数 FROM students

3 IFNULL

在Java程序中调用sql语句时,如果返回结果是null,是非常容易引发一些意外情况的。

因此,我们希望在SQL中做一些处理,如果查询结果是null,就转换为特定的值,这就要用到Mysql中IFNULL函数。

首先SQL一般写法是这样的:

SELECT  price FROM goods WHERE name='light';

使用IFNULL改写一下:

SELECT IFNULL(price,0) price FROM goods WHERE name='light';

但使用IFNULL语句,如果where条件中的name值是不存在的,那么仍将返回null,例如:

-- 返回结果:null
SELECT IFNULL(price,0) price FROM goods WHERE name='aaa';

这时候,需要改写成下面的形式:

-- 返回结果:0
SELECT IFNULL((SELECT price FROM goods WHERE name='aaa'),0) price;

在实际应用中,如果你确定where条件的值一定存在,使用前者就可以了,否则要用后者。

IFNULL函数也可以结合聚合使用,例如:

-- 返回结果:0
SELECT IFNULL(SUM(price),0) FROM goods WHERE status=3;

其他,AVGCOUNT等用同样方式处理,而且,无论where条件存在不存在,结果都是会返回0的。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 【Mysql学习之旅-2】经典sql面试题及答案分析

    1、学生表 student(s_id:学生id,s_name:学生姓名,s_birth:学生生日,s_sex:学生性别):

    云深i不知处
  • 【JMeter系列-6】JMeter BeanShell Sampler与JMeter BeanShell断言

    BeanShell是一种完全符合Java语法规范的脚本语言,但又拥有一些独有的语法和方法。(当然,也有一些不一样的地方,比如 BeanShell就不支持Java...

    云深i不知处
  • 【SpringBoot注解-5】web项目相关注解

    @RestController 是MVC中应用非常频繁的一个注解,也是 SpringBoot 新增的一个注解,包括:

    云深i不知处
  • 你会让人工智能为你做决定吗?

    问题导读 1.你认为我们已经将那些事情委托给人工智能? 2.你认为人工智能是否可以做更多的事情? 3.你认为人工智能未来可以做那些事情?

    用户1410343
  • Delphi 编写 数字签名验证 并获取签名信息

    一个客户想通过编程实现验证程序自身的数字签名来确保程序的完整性,防范病毒感染以及防止一些无聊人士的修改(通过十六进制编辑器替换一些版权、网址、LOGO..); ...

    战神伽罗
  • FPGA与LVDS信号兼容性分析方法

    很多工程师在使用Xilinx开发板时都注意到了一个问题,就是开发板中将LVDS的时钟输入(1.8V电平)连接到了VCCO=2.5V或者3.3V的Bank上,于是...

    碎碎思
  • Spring Boot配置特定属性spring.profiles

    lyb-geek
  • 搞定三大神器之 Python 装饰器

    装饰器,几乎各大Python框架中都能看到它的身影,足以表明它的价值!它有动态改变函数或类功能的魔力!

    double
  • 助力远程研发协作,TAPD免费开放企业版

    在这个不寻常的春节假期,新型肺炎疫情一直牵动着所有人的心。 在几天前,TAPD腾讯敏捷协作平台已与腾讯其他办公协同类产品一起,免费开放企业版线上协作能力,为抗...

    TAPD敏捷研发
  • Flutter环境搭建

    这几年,移动跨平台的趋势可以说是越来越明显,技术实现上也是百花争艳,不过究其实现,无外乎有那么几种。 Web 流:也被称为 Hybrid 技术,它基于 Web ...

    xiangzhihong

扫码关注云+社区

领取腾讯云代金券