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

select
    uid,
    count(exam_id) as exam_complete_cnt
from
    (
        select
            uid,
            start_time,
            submit_time,
            exam_id,
            DENSE_RANK() over (
                partition by
                    uid
                order by
                    uid,
                    DATE_FORMAT (start_time, '%Y-%m') desc
            ) as ranking
        from
            exam_record
    ) tc
where
    ranking in (1, 2, 3)
group by
    uid
having
    count(exam_id) = count(submit_time)
order by 	exam_complete_cnt desc,uid desc;

#先按照题目意思给出日期排名

        select
            uid,
            start_time,
            submit_time,
            exam_id,
            DENSE_RANK() over (
                partition by
                    uid
                order by
                    uid,
                    DATE_FORMAT (start_time, '%Y-%m') desc
            ) as ranking
        from
            exam_record

就三种排序类型，这里采用dense_rank()函数的模式，不间隔，同级连续排序

#序号函数，row_number() 按照顺序排序 | 唯一标识
rank() 同名排名相同,但是遇见同名会出现跳一位之后在进行排序，1 1  3 | 
dense_rank() 同名排名相同，且连续 1 1 2
select dname,ename,salary,row_number() over (partition by dname order by salary desc) as cn from employee;

#之后按照排名进行筛选，要求试卷数量就要用到分组聚合函数，最后是用试卷数量和我提交数量是否相等来作为是否为空的证据

select
    uid,
    count(exam_id) as exam_complete_cnt
from
    (
        select
            uid,
            start_time,
            submit_time,
            exam_id,
            DENSE_RANK() over (
                partition by
                    uid
                order by
                    uid,
                    DATE_FORMAT (start_time, '%Y-%m') desc
            ) as ranking
        from
            exam_record
    ) tc
where
    ranking in (1, 2, 3)
group by
    uid
having
    count(exam_id) = count(submit_time)
order by 	exam_complete_cnt desc,uid desc;