有这么一个表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
后的分组数据。
sql查询,结合group by
和case when
,子查询查询按照时间轴排序,某字段的标签新旧变化对比情况
遇到这样一个业务,需要按照时间轴的顺序展示,标签的新旧变化情况,
表结构如下:
sql语句如下:
SELECT
tagName,
CASE
WHEN b.tagTime > (SELECT min(tagTime) FROM IP_gene_info c WHERE c.ip = b.ip AND c.tagName = b.tagName)
THEN
(
SELECT
SUBSTRING_INDEX(GROUP_CONCAT(a.tagValue ORDER BY tagTime DESC),',',2)
as tagValue
FROM IP_gene_info a
WHERE a.ip = b.ip
AND a.tagName = b.tagName
AND a.tagTime <=b.tagTime
GROUP BY b.tagName
)
ELSE
max(tagValue)
END AS tagValue
FROM
IP_gene_info b
WHERE
b.ip = '117.18.237.29'
GROUP BY
b.tagName,
b.tagTime
ORDER BY
tagTime
group by case when
用法
select
的字段要和group by
的一样,并且group by
不能用别名
select
case when left(tagName,4)='http' then 'http' when left(tagName,3) = '数据库' then '数据库' else 0 end as tagName,
count(1)
from IP_gene_info
group by (case when left(tagName,4)='http' then 'http' when left(tagName,3) = '数据库' then '数据库'
else 0
end)