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

知识点

• 窗口函数dense_rank :生成1223类的连续数字排序

可以看一下这个子查询的执行结果，这是根据start_time进行排序。

为什么要用start_time呢？如果用submit_time来排序的话，因为我们要正确理解近三个月试卷全部完成的这个信息，用submit_time来排序的话，就会将未完成的那个试卷所在的月份也算进来了。

这里还不是很懂。

外层查询条件

where

recent_months <= 3

是因为我们只需要统计近三个月的

select
    uid,
    count(submit_time) as exam_complete_cnt
from
    (
        select
            uid,
            start_time,
            submit_time,
            dense_rank() over (
                partition by
                    uid
                order by
                    date_format (start_time, '%Y%m') desc
            ) as recent_months
        from
            exam_record
    ) recent_table
where
    recent_months <= 3
group by
    uid
having
    count(submit_time) = count(start_time)
order by
    exam_complete_cnt desc,
    uid desc