需求:按照渠道,城市,省份分组获取最高订单日,最高日订单数
思路:先获取分组后每组的订单排序,再给他们的排序附上号数,再链表获取1号的数据字段
SELECT o.fromType,o.provinceId,o.provinceName,o.cityId,o.cityName,SUM(o.orderNum) As orderNum ,SUM(o.orderSum) As orderSum
,SUM(o.accountNum) As accountNum,SUM(o.accountSum) As accountSum,SUM(o.cancelNum) As cancelNum,SUM(o.cancelSum) As cancelSum
from order o group by o.fromType,o.provinceId,o.cityId;
先获取其它需求需要的字段,
结果:
select sum(o.orderNum) as sumOrderNum,o.fromType,o.provinceId,o.cityId,o.statDate from order o where o.statDate between '2018-09-01' and '2018-10-20' group by o.statDate,o.fromType,o.provinceId,o.cityId order by o.fromType,o.provinceId,o.cityId,sumOrderNum desc;
获取按照城市,渠道,省份,日期分组后,按照总订单数倒序按分好组的顺序进行排序,代表着这一天这个城市这个省份城市渠道一共下了多少单,分在一起以总订单数倒序,order by后面跟的就是以什么字段的形式排序在一起,desc是根据哪个字段降序
结果:
select @rownum:=case when @fromType=fromType and @provinceId = provinceId and @cityId= cityId then @rownum+1 else 1 end as count_of_total,
@fromType:=t.fromType AS fromType,@provinceId:= t.provinceId AS provinceId,@cityId:= t.cityId AS
cityId,t.sumOrderNum as maxOrderNum,t.statDate as maxOrderDate from(select sum(o.orderNum) as sumOrderNum,o.fromType,o.provinceId,o.cityId,o.statDate from
order o where o.statDate between '2018-09-01' and '2018-10-20' group by o.statDate,o.fromType,o.provinceId,o.cityId order by o.fromType,o.provinceId,o.cityId,sum(o.orderNum)desc)t;
再给这些数据一个排名,按组的形式。
定义一个rownum变量一开始为空所以为1,固然一开始排好序,所以第一个肯定最大,然后获取后面的数据字段,一样就加1,排序下去,不一样就重新赋1,获取到排名,provinceId之类的值根据上个表获取
结果:
SELECT o.fromType,o.provinceId,o.provinceName,o.cityId,o.cityName,SUM(o.orderNum) As orderNum ,SUM(o.orderSum) As orderSum
,SUM(o.accountNum) As accountNum,SUM(o.accountSum) As accountSum,SUM(o.cancelNum) As cancelNum,SUM(o.cancelSum) As cancelSum ,maxOrderNum,maxOrderDate
from order o
left join(
select @rownum:=case when @fromType=fromType and @provinceId = provinceId and @cityId= cityId then @rownum+1 else 1 end as count_of_total,
@fromType:=t.fromType AS fromType,@provinceId:= t.provinceId AS provinceId,@cityId:= t.cityId AS
cityId,t.sumOrderNum as maxOrderNum,t.statDate as maxOrderDate from(select sum(o.orderNum) as sumOrderNum,o.fromType,o.provinceId,o.cityId,o.statDate from
order o where o.statDate between '2018-09-01' and '2018-10-20' group by o.statDate,o.fromType,o.provinceId,o.cityId order by o.fromType,o.provinceId,o.cityId,sum(o.orderNum)desc)t
)a
on a.fromType = o.fromType and a.provinceId = o.provinceId and a.cityId = o.cityId and a.count_of_total =1 where o.statDate between '2018-09-01' and '2018-10-20'
group by o.fromType,o.provinceId,o.cityId;
再和原来的字段来个链表查询合在一起
结果: