题解 | #近三个月未完成试卷数为0的用户完成情况#

这题就是一个很基础得题了，逻辑简单，应该都会。题目中的描述还是比较清楚，就不过多打一堆废话。

这里用得变量去做，开窗自己去开

select
d.uid,sum(d.startNum) as exam_complete_cnt
from 
(
    select
    b.uid,b.startNum,b.submitNum, (case when @preCol = b.uid then @rk:=@rk+1 else @rk:=1 end) as rk,@preCol:=b.uid
    from 
    (
        select
        a.uid, date_format(start_time,"%Y%m") as date,count(1) as startNum, 
        sum(case when a.submit_time is null then 0 else 1 end) as submitNum
        from exam_record a
        group by a.uid, date_format(start_time,"%Y%m") 
    ) b ,(Select @preCol:=NULL,@rk:=0) c

    order by b.uid desc,b.date desc
) d where d.rk <= 3

group by d.uid having sum(d.startNum) = sum(d.submitNum)

order by exam_complete_cnt desc,d.uid desc