mysql的count和sum使用条件表达式

count函数条件不为null的时候显示结果。即使为false也也会显示结果。 可以是使用if条件或者case when语句。如果条件不为null即需要的结果。

使用count()函数实现条件统计的基础是对于值为NULL的记录不计数，常用的有以下三种方式，假设统计num大于200的记录

select count(num > 200 or null) from a; select count(if(num > 200, 1, null)) from a select count(case when num > 200 then 1 end) from a

sum函数是条件为True才返回结果。 下面结果等效

select name,COUNT(CASE WHEN score<60 THEN 1 END) as sum_score,AVG(score) as avg_sorce from tb_stu GROUP BY name HAVING SUM(score<60)>=2; select name,COUNT(if(score<60,1,null)) as sum_score,AVG(score) as avg_sorce from tb_stu GROUP BY name HAVING SUM(score<60)>=2; select name,sum(score<60) as sum_score,AVG(score) as avg_sorce from tb_stu GROUP BY name HAVING SUM(score<60)>=2;

转载于:https://www.cnblogs.com/c-x-a/p/11074369.html

相关资源：JAVA上百实例源码以及开源项目