一对多关系下取得特定关联一对一记录

问题来源

假设我要查课程表和一个保存有历年分数线的及格表，利用两个表得到一个最新年度或者最高峰值的科目分数记录，问题来了，一对多的关系怎么取得一对一的记录行？ lesson（课程）:
lesson_pass（历年科目合格分记录）:

方法一（简单粗暴）

利用MAX()来得到最大值，并进行连接得到新表，该表就是一对一的记录。

--查询每科最高分数线
SELECT * FROM lesson 
LEFT JOIN (
SELECT lesson_id,MAX(pass_line) as pass_line FROM lesson_pass GROUP BY lesson_id
)mvp
ON (lesson.lesson_id = mvp.lesson_id)

得到结果
那如果要统计出每科的最新分数线怎么办？实现起来确实有点难，那就把一对多的多先简化成一吧，得到中间表再连接中间与记录得到一对一的科目最新分数线。（前提是有能够排序/自增长的字段，能证明是最新，这里使用自增长的log_id来证明，因为记录的id在没有人为改动的情况下肯定是新的比旧的大）

--查询每科最新分数线
SELECT lesson.*,lesson_pass.log_id,lesson_pass.pass_line FROM lesson 
LEFT JOIN (
SELECT lesson_id,MAX(log_id) as log_id FROM lesson_pass GROUP BY lesson_id
)mvp ON (lesson.lesson_id = mvp.lesson_id)
LEFT JOIN 
lesson_pass 
ON (mvp.log_id = lesson_pass.log_id)

得到结果

方法二

其实大家也看到，要是查询最新分数线，其实限制挺大的，幸亏有些数据服务出了分组聚合函数，通过它可以实现分类统计特定条件的最值。中间表：

SELECT *,ROW_NUMBER() OVER(partition BY lesson_id ORDER BY time DESC)rn FROM lesson_pass

中间结果：（实际按归类的每组生成了行序号rn，通过这个rn可以做很多事情）

然后对中间表进行连接得到最终想要的结果。

总结

其实在有提供函数的情况下，我更推荐使用函数，不仅高大上，而且用途更广泛。