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

希望大家注意一下这里的dense_rank 函数，我一开始以为rank也可以结果就出现了下面的错误

from (select uid,score,submit_time,
rank() over (partition by uid order by DATE_FORMAT(start_time,'%Y%m')desc)as ranking
from exam_record)as new_exam_record
where ranking<=3
group by uid
having count(score)=count(uid)
order by exam_complete_cnt desc,uid desc

结果是错误的 问题的关键在于需要使用dense_rank 函数，只需要把rank改成dense_rank 函数就行了

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