通过代码1

WITH t as (
SELECT
    u_i.uid,
    level,
    register_time,
    tag,
    job,
    submit_time,
    score,
    max(e_r.score)over(
        partition by uid
    ) maxs
FROM
    examination_info e_i
LEFT JOIN
    exam_record e_r
ON
    e_r.exam_id = e_i.exam_id
RIGHT JOIN
    user_info u_i
ON
    u_i.uid = e_r.uid
)


SELECT
    uid,
    level,
    register_time,
    maxs
FROM
    t
WHERE
    uid in(
        select
            uid
        from
            t
        where
            tag = '算法' and
            job = '算法' and
            date(register_time) = date(submit_time)
    )
GROUP BY
    uid
ORDER BY
    maxs DESC
limit
    6,3

思路

找到求职方向为算法工程师，且注册当天就完成了算法类试卷的人，按参加过的所有考试最高得分排名。排名榜很长，我们将采用分页展示，每页3条，现在需要你取出第3页（页码从1开始）的人的信息。

注意：

我们要取得是注册当天就完成算法类的人的所有试卷成绩

为了更清晰一点这里就搞得麻烦一点

将用户分组的所有的试卷最高分查出来

然后where in 查询符合条件的人

最后limit分页查询

过程很简单啊，这里就不分步完成了。

limit分页用法：

假如要显示的页数为page，每一页条目数为size select 查询列表 from 表 limit (page-1)*size,size;

通过代码2

select 
    uid,
    level,
    register_time,
    max_score
from 
(select 
    ui.uid  uid,
    level,
    register_time,
    max(score)  max_score,
    row_number() over(order by max(score) desc)  rank_score
from 
    exam_record er
left join
    user_info ui 
on 
    ui.uid = er.uid
left join 
    examination_info ei 
on 
    er.exam_id = ei.exam_id
where 
    job = "算法" 
    and tag = "算法" 
    and date(register_time) = date(submit_time)
group by 
    uid
order by 
    max_score desc
) t
LIMIT
    6,3