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

limit

• limit m,n——取第m+1至m+n行
• limit n=limit 0,n——取前N行

select q.uid,level,register_time,max_score
from
  (select *,max(score) over (partition by uid) as max_score
   from exam_record) as q
   join user_info as ui
   on ui.uid=q.uid
   join examination_info as ei
   on q.exam_id=ei.exam_id
   where job='算法' and tag='算法' and 
   date(register_time)=date(submit_time)
order by max_score desc
limit 6,3

row_number() over

select uid,level,register_time,max_score
from 
(select uid,level,register_time,   
 row_number() over(order by max_score) as rn
 from
    (select er.uid,level,register_time,max(score) over 
     (partition by er.uid) as max_score
    from exam_record as er
     join user_info as ui 
     on er.uid=ui.uid
     where er.uid in 
     (select er.uid
    from exam_record as er
    join user_info as ui
    on ui.uid=er.uid
    join examination_info as ei
    on er.exam_id=ei.exam_id
     where job='算法' and date(register_time)=date(submit_time) 
      and tag='算法' ) ) as q1) as q2
where rn between 7 and 9