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

以你之姓@ 2023-10-18 20:39 61阅读 0赞

## 前言 ##

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

## 1 CASE WHEN ##

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

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

**1、简单函数**

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

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

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

表score：  
![在这里插入图片描述][2020071617145343.png]  
**场景**：在score表中，sex为1表示男性，sex=0表示女性，查询时转换成汉字显示。

**SQL语句**：

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

**结果**：  
![在这里插入图片描述][20200716171040763.png]  
**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

**结果**：  
![在这里插入图片描述][20200716175632839.png]  
**3、综合使用**

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

**先看第1个场景**

在下表`score`（sex=1为男，sex=0为女）中，统计有多少个男生和女生以及男女生及格的各有多少个。  
![在这里插入图片描述][2020071617145343.png]  
**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;

**结果**：  
![在这里插入图片描述][20200716195527268.png]  
**再看第2个场景**  
![在这里插入图片描述][20200716200406544.png]  
将上面的score表转换为下面形式：  
![在这里插入图片描述][2020071620101848.png]  
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;

结果如下：  
![在这里插入图片描述][20200716194429328.png]

## 2 IF ##

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

IF(expr,result_true,result_false)

expr是一个条件表达式，如果结果为true，则返回result\_true，否则返回result\_false。

用一个示例演示，还是表score：  
![在这里插入图片描述][2020071617145343.png]  
使用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

![在这里插入图片描述][20200719164531321.png]

## 3 IFNULL ##

在Java程序中调用sql语句时，如果返回结果是`null`，是非常容易引发一些意外情况的。  
![在这里插入图片描述][20190628105032354.png]  
因此，我们希望在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;

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

[2020071617145343.png]: https://img-blog.csdnimg.cn/2020071617145343.png
[20200716171040763.png]: https://img-blog.csdnimg.cn/20200716171040763.png
[20200716175632839.png]: https://img-blog.csdnimg.cn/20200716175632839.png
[20200716195527268.png]: https://img-blog.csdnimg.cn/20200716195527268.png
[20200716200406544.png]: https://img-blog.csdnimg.cn/20200716200406544.png
[2020071620101848.png]: https://img-blog.csdnimg.cn/2020071620101848.png
[20200716194429328.png]: https://img-blog.csdnimg.cn/20200716194429328.png
[20200719164531321.png]: https://img-blog.csdnimg.cn/20200719164531321.png
[20190628105032354.png]: https://img-blog.csdnimg.cn/20190628105032354.png