MySQL按字段分组后如何获取每组最新的2条记录？

不需要编写2条查询语句，单条查询即可实现按instrument分组取每组最新2条记录的需求，两种适配不同MySQL版本的实现方案如下：

MySQL 8.0+ 版本（推荐，窗口函数实现） #

使用ROW_NUMBER()窗口函数按instrument分区、按自增id倒序排序，筛选排序序号前2的记录即可，row_num = 1为每组最新记录，row_num = 2为次新记录：

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY instrument ORDER BY id DESC) AS row_num
    FROM tbl_prices
) AS temp_tbl
WHERE row_num <= 2
ORDER BY instrument, row_num;

如果需要直接计算同组最新两条的差值，可直接在查询基础上做聚合，不需要二次查询，示例（假设差值计算字段为price）：

SELECT 
    instrument,
    -- 最新价减次新价得到差值
    MAX(IF(row_num = 1, price, NULL)) - MAX(IF(row_num = 2, price, NULL)) AS latest_price_diff
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY instrument ORDER BY id DESC) AS row_num
    FROM tbl_prices
) AS temp_tbl
WHERE row_num <= 2
GROUP BY instrument;

MySQL 5.x 版本（无窗口函数支持，关联子查询实现） #

通过同表关联统计同分组内id大于等于当前记录的行数，行数小于等于2的即为分组内最新的2条记录：

SELECT p1.*
FROM tbl_prices p1
WHERE (
    SELECT COUNT(*)
    FROM tbl_prices p2
    WHERE p2.instrument = p1.instrument AND p2.id >= p1.id
) <= 2
ORDER BY p1.instrument, p1.id DESC;

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