有这么一个表Price,用来描述每天每种产品售出的金额,具体内容如表1.1所示,其中date字段代表日期,type字段表示产品类型,price字段表示产品售出金额。
date | type | price |
---|---|---|
2020-07-13 | 0 | 33.30 |
2020-07-13 | 0 | 20.00 |
2020-07-12 | 1 | 21.00 |
2020-07-13 | 1 | 54.00 |
2020-07-12 | 1 | 11.00 |
2020-07-12 | 0 | 30.00 |
现要做一个统计,统计每天每种产品类型的总售出金额,期望结果如下表所示,以日期date作为分组依据,分别计算每种产品类型在当天的售出总金额。
date | type0_price | type1_price |
---|---|---|
2020-07-12 | 30.00 | 32.00 |
2020-07-13 | 53.30 | 54.00 |
由需求可知,需要对日期和产品类型进行分组,再计算price的总和,可是如果我们直接用group by会产生怎样的效果?代码如下
select date,type,sum(price)
from Price
group by date,type;
得出的结果如下:
date | typ | sum(price) |
---|---|---|
2020-07-12 | 0 | 30.00 |
2020-07-12 | 1 | 32.00 |
2020-07-13 | 0 | 53.30 |
2020-07-13 | 1 | 54.00 |
可以看出计算结果和我们期望的是一致的,但是排列方式却有出入,直接求出的结果是按列排列的,而我们需要的是按行排列的。那么如何达到我们预期的效果呢?
一个方法是使用临时表,通过分别计算每种产品类型的每日售出总金额,再对每个结果进行汇总便能达到要求,具体代码如下:
select t1.date,t1.type0_price,t2.type1_price
from
(select date,sum(price) as type0_price from Price where type = 0 group by date) t1
join
(select date,sum(price) as type1_price from Price where type = 1 group by date) t2
on t1.date = t2.date;
得到结果如下:
date | type0_price | type1_price |
---|---|---|
2020-07-12 | 30.00 | 32.00 |
2020-07-13 | 53.30 | 54.00 |
是我们预期所达到的效果,而且代码看着也较为简洁,可它存在着一个问题,当产品类型type的种类越来越多了怎么办?难道要继续join无数个表下去吗,当然可以是可以的,就是太费时费力了,不值得。因此我们需要一个更为简洁的方法。
另一种方法便是group by 与case when 的结合使用,直接看如下代码
select
date
,sum(case when type=0 then price end) as type0_price
,sum(case when type=1 then price end) as type1_price
from Price
group by date;
得到的结果如下:
date | type0_price | type1_price |
---|---|---|
2020-07-12 | 30.00 | 32.00 |
2020-07-13 | 53.30 | 54.00 |
也是我们想要的结果,貌似代码量与第一种方法差不多,但是当type变多时,这种方法只需要继续加一个sum就可以实现,并且不需要创建额外的临时表进行操作,更加方便。
总结:在sql语句中有使用到group by 时,select通常只能选择该进行group by的字段以及 sum、count、avg、max、min等聚合函数,经过测试,在这些聚合函数中,都可以使用case when进一步进行条件约束,作用域都为经过group by后的分组数据。