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

SELECT uid, COUNT(score) AS exam_complete_cnt
FROM (
    SELECT uid, score, start_time,
        DENSE_RANK() OVER (PARTITION BY uid ORDER BY DATE_FORMAT(start_time,"%Y%m") DESC) AS `rank`
    FROM exam_record
) AS ranked_records
WHERE `rank` <= 3 
GROUP BY uid
HAVING COUNT(score) = COUNT(start_time)
ORDER BY exam_complete_cnt DESC, uid DESC;

注意点:

① COUNT(<某列>)时，如果该列包含NULL值，会忽略NULL进行计数；COUNT(*)时，会包含NULL值进行计数

② DENSE_RANK, RANK, ROW_NUMBER的区别：由于是按DATE_FORMAT(start_time,"%Y%m")进行排序，那么例如出现20211001，20210905，20210906，20210504的情况。

RANK得到的结果是: 1, 2, 2, 4

DENSE_RANK得到的结果是: 1, 2, 2, 3

那么根据题目的意思是，近三个有作答试卷的月份，那么应当是DENSE_RANK的情况

解题思路：

① 创建一个按日期降序排列的排序表，用于查找到题目中要求的近三个有作答试卷的月份

② 通过WHERE `rank` <= 3 实现近三个有作答试卷的月份

通过HAVING COUNT(score) = COUNT(start_time) 保留那些作答且完成的记录