(consign_day int comment “揽收日期” ,waybill_no varchar(30) “运单号“,class varchar(10) “托寄物类型”,userid varchar(20) comment “会员号”),请统计各个会员在4月份揽收的文件、娱乐、食品的件量以及主要托寄物类型(托寄物类型件量最多的就是主要托寄物类型)
waybill_constype中waybill_no是运单号,class是托寄物类型()
-- 第一部分: -- 各个会员的托寄类型分别是文件、娱乐和食品的件量 select userid, sum(case when class = '文件' then 1 else 0 end) as sum_wenjian, sum(case when class = '娱乐' then 1 else 0 end) as sum_yule, sum(case when class = '食品' then 1 else 0 end) as sum_shipin from waybill_constype where substr(consign_day,5,2) = '04' group by userid; -- select userid, -- row_number() over(partition by userid order by class desc) -- from waybill_constype; -- 第二部分: -- 可以使用窗口函数处理,因为我的工具是MySQL,该版本还不支持窗口函数,故 -- 使用非窗口函数 -- 对每个会员的每种类型进行数量降序操作 select userid,class,count(userid) as class_nums from waybill_constype where substr(consign_day,5,2) = '04' group by userid,class order by userid,count(userid) desc ; -- 先获取每个会员的不同托运类型的最大件数 select a.userid,max(class_nums) as class_max_num from ( select userid,class,count(userid) as class_nums from waybill_constype where substr(consign_day,5,2) = '04' group by userid,class order by userid,count(userid) desc )a group by a.userid; -- 将上面两个结果进行拼接 select t1.userid,t1.class,t1.class_nums from ( select userid,class,count(userid) as class_nums from waybill_constype where substr(consign_day,5,2) = '04' group by userid,class order by userid,count(userid) desc )t1 join ( select a.userid,max(class_nums) as class_max_num from ( select userid,class,count(userid) as class_nums from waybill_constype where substr(consign_day,5,2) = '04' group by userid,class order by userid,count(userid) desc )a group by a.userid )t2 on t1.userid = t2.userid and t1.class_nums = t2.class_max_num; -- 最终将 第一部分 和 第二部分 进行表连接处理 -- 得到最终的结果: select tt1.userid,tt1.sum_wenjian,tt1.sum_yule,tt1.sum_shipin, tt2.class,tt2.class_nums from ( select userid, sum(case when class = '文件' then 1 else 0 end) as sum_wenjian, sum(case when class = '娱乐' then 1 else 0 end) as sum_yule, sum(case when class = '食品' then 1 else 0 end) as sum_shipin from waybill_constype where substr(consign_day,5,2) = '04' group by userid ) tt1 join ( select t1.userid,t1.class,t1.class_nums from ( select userid,class,count(userid) as class_nums from waybill_constype where substr(consign_day,5,2) = '04' group by userid,class order by userid,count(userid) desc )t1 join ( select a.userid,max(class_nums) as class_max_num from ( select userid,class,count(userid) as class_nums from waybill_constype where substr(consign_day,5,2) = '04' group by userid,class order by userid,count(userid) desc )a group by a.userid )t2 on t1.userid = t2.userid and t1.class_nums = t2.class_max_num ) tt2 on tt1.userid = tt2.userid ;
select userid, class, count(class) as count from waybill_constype where consign_day in (20180401, 20180430) group by userid;
select class, max(count) from ( select userid, class, count(class) as count from waybill_constype where consign_day in (20180401, 20180430) group by class) a order by count desc limit 1;