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

一开始的错误：

SELECT uid, COUNT(submit_time) exam_complete_cnt FROM exam_record
WHERE TIMESTAMPDIFF(MONTH, start_time, 
                    (SELECT MAX(start_time) FROM exam_record GROUP BY uid)) < 3
GROUP BY uid
HAVING COUNT(submit_time) = COUNT(start_time)
ORDER BY exam_complete_cnt DESC, uid DESC;

报错'Subquery returns more than 1 row'。SELECT MAX(start_time) FROM exam_record GROUP BY uid)不能这么写，因为他返回了多行。

正确答案：

SELECT uid, COUNT(submit_time) 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) rk
FROM exam_record) t1
WHERE t1.rk<=3
GROUP BY uid
HAVING COUNT(submit_time) = COUNT(start_time)
ORDER BY exam_complete_cnt DESC, uid DESC;

要点是窗口函数的使用