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

方法1：dense_rank
with a as (
    select uid,start_time,submit_time,
           dense_rank() over(partition by uid order by date_format(start_time,"%Y%m") desc) rank_current
    from exam_record)

select uid,
       count(submit_time) exam_complete_cnt
from a
where rank_current<=3 
group by uid 
having count(start_time)=count(submit_time)
order by exam_complete_cnt desc,uid desc

方法2：select() 代替 dense_rank
select uid,count(submit_time) exam_complete_cnt
from
    (select d1.uid,d1.start_time,d1.submit_time,
        (select count(distinct date_format(d2.start_time,"%Y%m"))
        from exam_record d2
        where date_format(d2.start_time,"%Y%m")> date_format(d1.start_time,"%Y%m") and d2.uid=d1.uid)+1 rank_current
    from exam_record d1) e
where rank_current<=3
group by uid
having count(start_time)=count(submit_time)
order by exam_complete_cnt desc,uid desc