我们写的SQL, 都是整行数据操作的, 如果要操作某列的数据, 就需要使用到 聚合函数了
count与sum都是聚合函数:
count() 统计数据表 总数据条数.
sum() 求某列值的: 和
而在某种情况下,count与sum都能够用来计数,但带来信息十分具有迷惑性,非常有趣,请看以下例子。
-- 需求: 输出报表,统计不同供应商供应商品的总库存量,以及高价值商品的种类个数(单价超过40定义为高价值) -- 结果显示四列: -- 供应商ID supplier_id -- 供应商公司名 company_name -- 由该供应商提供的总库存 all_units -- 由该供应商提供的高价值商品库存的种类个数 expensive_units
使用sum计数高价值商品的种类个数
select p.supplier_id,company_name,sum(units_in_stock) all_units,
sum(if(unit_price > 40, 1,0)) as expensive_units
from products p join suppliers s on p.supplier_id = s.supplier_id group by p.supplier_id;
将产品表与供应商表以supplier_id(供应商编号)作为关联条件进行内连接,并按照supplier_id进行分组。如果存在一种商品的unit_price(单价)> 40,则记1,否则记为0,以此累加。
结果如图
以第一行名为“Exotic Liquids”的公司为例,supplier_id = 1 , 可以明显看出expensive_units=0。说明此公司供应的所有商品的unit_price(单价)<40。
使用count计数高价值商品的种类个数
select p.supplier_id,company_name,sum(units_in_stock) all_units,
count(if(unit_price > 40, 1,0)) as expensive_units
from products p join suppliers s on p.supplier_id = s.supplier_id group by p.supplier_id;
其他条件没有任何改变,只将sum改成了count。
如果存在一种商品的unit_price(单价)> 40,则记1,否则记为0,以此计数。
结果如图
还是以这个名为“Exotic Liquids”的公司为例,supplier_id = 1 , 就会发现一个很神奇的情况。
为什么这次,expensive_units=3?
这个3是从哪来的呢?这个名为“Exotic Liquids”的公司,供应的商品的价格到底有没有>40呢?
这时候相信很多读者看到这,都想看一下供应商品价格>40的信息,到底是什么情况。那么咱们就来验证一下。
select supplier_id,unit_price,units_in_stock from products where unit_price>40;
结果如图
查看了所有unit_price>40的信息,可以看出supplier_id(供应商id)并没有出现1,也就是名为“Exotic Liquids”的公司根本就没有供应unit_price>40的商品。那么他到底供应了什么样的商品呢?
select supplier_id,unit_price,units_in_stock from products where products.supplier_id = 1 ;
结果如图
明明 “Exotic Liquids”的unit_price分别为18,19,10,那么为什么采取count计数会出现3,而用sum计数还是0呢?
总结
我们忽略了count与sum的一个区别
sum(if(unit_price > 40, 1,0)) as expensive_units
count(if(unit_price > 40, 1,0)) as expensive_units
当利用sum时,只要出现满足条件的值我们就记为1,没有则记为0,依次累加。
当利用count时,只要出现满足条件的值我们就记为1,没有则记为0,依次累加。
但是
事实上,并非如此
利用count进行计数,满足条件则记为1
当不满足条件时,会记为0
而count会统计所有满足条件的值的次数 以及 非null的次数
采用sum则不会有这个问题
满足为1
不满足为0
最终加总,该是多少就是多少
所以想要呈现出与sum一样的效果,只需要将count(if(unit_price > 40, 1,0)) as expensive_unit改为
count(if(unit_price > 40, 1,null))
拓展
面试题: count(*), count(1), count(列)的区别?
区别1: 统计范围不同.
count(列): 只统计该列的 非null值.
count(*), count(1): 无论是否为null, 都统计.
区别2: 效率不同.
从高到低分别是: count(主键列) > count(1) > count(*) > count(普通列)