Mysql分组后以最大值为条件获取其他字段

需求：按照渠道，城市，省份分组获取最高订单日，最高日订单数

思路：先获取分组后每组的订单排序，再给他们的排序附上号数，再链表获取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;

再和原来的字段来个链表查询合在一起

结果：