顺丰大数据挖掘及分析第二道sql题

select g.Userid,g.Class,g.Classnum,f.mainclass
from
(select Userid,Class,count(Class) as Classnum
from waybill_constype
where Consign_day >= "20180401" and Consign_day <="20180430"
group by Userid,Class) g
left join
(select e.Userid,e.mainclass
from
(select d.Userid,
case 
  when d.Classnum = d.maxnum  then d.Class
  else null
end as mainclass
(select c.Userid,c.Class,c.Classnum,d.maxnum
from
(select Userid,Class,count(Class) as Classnum
from waybill_constype
where Consign_day >= "20180401" and Consign_day <="20180430"
group by Userid,Class) c
left join 
(select a.Userid,max(a.Classnum) as maxnum
from 
(select Userid,Class,count(Class) as Classnum
from waybill_constype
where Consign_day >= "20180401" and Consign_day <="20180430"
group by Userid,Class) a
group by a.Userid) b
on c.Userid = b.Userid) d) e
where e.mainclass != null) f
on g.Userid = f.Userid#数据挖掘#