如何为line表每行获取对应line_version表的N条最新（最大ID）记录？

需求说明 #

line表的每一行对应line_version表中的多条记录，需要按line_id分组，为每组获取N条ID值最大的最新line_version记录（示例中N=2）。

示例数据 #

line_version表数据如下：

id   | line_id 
-------+---------
 10    |   1
 20    |   1
 30    |   1
 40    |   1
 50    |   2
 60    |   2
 70    |   2

期望结果 #

按line_id分组取每组2条最新记录，最终得到的id为：30, 40, 60, 70

具体实现方法 #

方法1：窗口函数（推荐，适配多数现代数据库） #

使用ROW_NUMBER()窗口函数按line_id分组，对每组内的记录按id倒序排序，筛选出排序序号≤N的记录即可：

SELECT id, line_id
FROM (
    SELECT 
        id, 
        line_id,
        ROW_NUMBER() OVER (PARTITION BY line_id ORDER BY id DESC) AS rn
    FROM line_version
) t
WHERE rn <= 2;

如果同组内存在ID相同的记录，且希望这些记录都被保留，可以用RANK()或DENSE_RANK()替换ROW_NUMBER()。

方法2：关联子查询（兼容低版本数据库） #

通过子查询统计当前记录在同组内比它ID大的记录数量，若数量小于N则保留该记录：

SELECT lv1.id, lv1.line_id
FROM line_version lv1
WHERE (
    SELECT COUNT(*) 
    FROM line_version lv2 
    WHERE lv2.line_id = lv1.line_id AND lv2.id > lv1.id
) < 2
ORDER BY lv1.line_id, lv1.id DESC;

这种写法适合不支持窗口函数的老版本数据库，但数据量较大时性能不如窗口函数。

方法3：MySQL 5.x专属写法（利用用户变量） #

针对不支持窗口函数的MySQL 5.x版本，可通过用户变量实现分组排序筛选：

SELECT id, line_id
FROM (
    SELECT 
        id, 
        line_id,
        @rn := IF(@current_line = line_id, @rn + 1, 1) AS rn,
        @current_line := line_id
    FROM line_version, (SELECT @current_line := NULL, @rn := 0) vars
    ORDER BY line_id, id DESC
) t
WHERE rn <= 2;

注意必须保证ORDER BY的顺序正确，确保变量能按分组和排序逻辑正常赋值。

内容的提问来源于stack exchange，提问作者Andrius