【MySQL】count(表达式)和sum(表达式)在计数上的区别

需求

统计所有没买过ipone 但是买过 S8的买家id。题解用了group by，所以只关心having后的表达式

sum(表达式)

select b.buyer_id from Product a join Sales b on a.product_id = b.product_id
group by b.buyer_id 
having sum(IF(a.product_name = 'iphone',1,0)) = 0 and sum(IF(a.product_name = 'S8',1,0)) > 0;

等价的sum(表达式) 简化写法

select b.buyer_id from Product a join Sales b on a.product_id = b.product_id
group by b.buyer_id 
having sum(a.product_name = 'iphone') = 0 and sum(a.product_name = 'S8') > 0;

等价的count(表达式)

select b.buyer_id from Product a join Sales b on a.product_id = b.product_id
group by b.buyer_id 
having count(IF(a.product_name = 'iphone',1,null)) = 0 and count(IF(a.product_name = 'S8',1,null)) > 0;

区别

count(*) 返回的结果集中的行数。COUNT(*)函数计算包含NULL和非NULL值的行，即：所有行
count(字段) 返回该字段不为NULL的所有行
count(字段 = ’iphone‘) 本质上是 count(表达式)

• 表达式不是NULL，无论真假不管表达式是否为true，计数都会+1，如select count(1>2)的结果为1，所以count(product_name = ‘S8’)并不能筛选出满足条件的记录。
• 如果count想要达到sum一样的功能，需要把表达式为FALSE变为NULL的语义count(IF(a.product_name = 'iphone',1,null))

参考

https://blog.csdn.net/u012660464/article/details/78623038
https://leetcode-cn.com/problems/sales-analysis-ii/solution/fen-xi-ge-ge-fen-zu-nei-bu-de-xin-xi-yao-zilw/