题解 | #近三个月未完成试卷数为0的用户完成情况#
近三个月未完成试卷数为0的用户完成情况
http://www.nowcoder.com/practice/4a3acb02b34a4ecf9045cefbc05453fa
select a.uid,count(a.uid) as exam_complete_cnt
from
(
select uid,exam_id,start_time,submit_time,score,
dense_rank() over(partition by uid order by DATE_FORMAT(start_time,'%Y%m') desc) rn --对各学生分组对考试月份按照最近时间排序
from exam_record
)a
where rn <=3 -- 选出最近三月
group by a.uid
having count(a.uid) = count(a.score ) --最近三月里参加的考试数等于完成的考试数的用户
order by exam_complete_cnt desc,a.uid desc; --对完成数目排序，再对学生ID排序
