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

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 month_NO
    FROM exam_record) AS S1
WHERE month_NO <=3
GROUP BY uid
HAVINg COUNT(start_time) = COUNT(submit_time) 
order by exam_complete_cnt desc, uid desc

比较巧妙的用法:COUNT(start_time) = COUNT(submit_time) 来筛选出试卷全部完成的用户!!