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