在oracle数据库中，有一张表waybill_consty

想的很基础，一步一步来的，暂未想优化做法，各位前辈如有好方法，可以多多指教~

-- 第一部分：
-- 各个会员的托寄类型分别是文件、娱乐和食品的件量
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 ;