题解 | #注册当天就完成了试卷的名单第三页#

-- 1.选取考算法的作答记录
with t as(SELECT er.uid,er.exam_id,er.start_time,er.submit_time,er.score,ei.tag from exam_record er join examination_info ei ON er.exam_id = ei.exam_id
where tag = '算法' )

-- 3.按照分数选取成绩最高的那条记录；排序分数，选取第三页的三个。
select uid,level, register_time ,score FROM(
-- 2.选取报考算法的作答记录且注册当天就完成算法试卷的考生；对考试分数按照uid排序
select t.uid,t.score,ui.level,ui.register_time,
row_number() over(PARTITION by uid ORDER BY score desc) as max_score
from t join user_info ui on t.uid = ui.uid
where job = '算法'
and DATE_FORMAT(t.submit_time,'%Y%m%d') = DATE_FORMAT(ui.register_time,'%Y%m%d')) q 
where max_score = 1
ORDER BY score DESC
limit 6,3