Mysql中条件判断用法CASE-WHEN,IF,IFNULL总结

        事情初始是,在开发中有一个需求,根据某个字段匹配某个值时统计该值出现的总数,这段话可能有点绕,简单举个例子,

        有如下数据表,

在score表中，sex为1表示男性，sex=0表示女性，查询时转换成汉字显示

这时候就可以用mysql中的case函数来实现

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

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

简单函数

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

SQL语句

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

结果

条件表达式

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

结果

综合使用

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

在下表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;

结果

 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;

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

OK,接下来自己上手操作

SELECT
IFNULL(SUM(CASE tbmachine.machine_type
	WHEN 'DEV-HL' THEN
		1
	ELSE
		0
	END 
) ,0) as 'machineNum'
FROM
	toymachine.tbmachine;

结果如下 

 

与聚合函数查询结果比对

SELECT
	count(m.uk_key_id)
FROM
	toymachine.tbmachine m
WHERE
	m.machine_type='DEV-HL';

这里直接统计主键数量, 

 

继续验证结果正确性

分别统计每种类型的数量

SELECT
	 m.machine_type,COUNT(m.machine_type)
FROM
	toymachine.tbmachine m
GROUP BY
	m.machine_type;

 

OK,结果一致,大功告成