明确题意:
找到求职方向为算法工程师,且注册当天就完成了算法类试卷的人,按参加过的所有考试最高得分排名。每页3条,需取出第3页(页码从1开始)的人的信息。
问题分解:
- 内连接用户信息表和试卷作答表:user_info JOIN exam_record USING(uid)
- 继续内连接试卷信息表:JOIN examination_info USING(exam_id)
- 继续内连接每个人的最高分表:
- 按用户分组,计算分数的max:MAX(score) AS max_score;GROUP BY uid
- 筛选满足条件的记录:
- 求职方向为算法工程师:WHERE job = '算法'
- 算法类试卷:tag = '算法'
- 注册当天完成的试卷:DATE(register_time)=DATE(submit_time)
- 选取第三页的3条,即偏移/跳过前6条,取三条:LIMIT 6,3
细节问题:
- 按最高分降序:ORDER BY max_score DESC
完整代码:
SELECT uid, `level`, register_time, max_score
FROM user_info
JOIN exam_record USING(uid)
JOIN examination_info USING(exam_id)
JOIN (
SELECT uid, MAX(score) AS max_score
FROM exam_record
GROUP BY uid
) AS t_exam_max_score USING(uid)
WHERE job = '算法' AND tag = '算法' AND DATE(register_time)=DATE(submit_time)
ORDER BY max_score DESC
LIMIT 6,3;