MySQL按FK分组获取每组最新（最大id）数据的SQL查询问题

我在MySQL中有一张名为test的表，表结构如下：

|id (int)|merchant (int)|eta (date)|FK (int)|

需求：按FK字段分组，返回每组中id最大（最新）的完整行数据。

示例数据 #

--------------------
|id|merchant|eta|FK|
|1|1|2022-11-07|1920|
|2|2|2022-11-08|1920|
|3|1|2022-11-09|1920|
|4|3|2022-11-07|1921|
|5|1|2022-11-07|1921|
|6|1|2022-11-07|1920|

期望结果 #

|5|1|2022-11-07|1921|
|6|1|2022-11-07|1920|

尝试的SQL及错误结果 #

我尝试的SQL语句：

SELECT
id, merchant, eta, FK
FROM test   
GROUP BY FK ORDER BY id DESC 

返回的错误结果：

|4|3|2022-11-07|1921|
|1|1|2022-11-07|1920|

调试用建表及插入语句 #

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `merchant` int(11) NOT NULL,
  `eta` date NOT NULL,
  `FK` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `test` (`id`, `merchant`, `eta`, `FK`) VALUES
(1, 1, '2022-11-07', 1920),
(2, 2, '2022-11-08', 1920),
(3, 1, '2022-11-09', 1920),
(4, 3, '2022-11-07', 1921),
(5, 1, '2022-11-07', 1921),
(6, 1, '2022-11-07', 1920);

问题原因 #

直接GROUP BY FK时，若MySQL关闭了ONLY_FULL_GROUP_BY模式，会返回每组的任意一行数据，并非id最大的行，因此得到不符合预期的结果。

解决方法 #

方法1：子查询关联获取最大id #

先按FK分组查询每组的最大id，再关联原表提取完整行数据：

SELECT t.*
FROM test t
INNER JOIN (
    SELECT FK, MAX(id) AS max_id
    FROM test
    GROUP BY FK
) t_max ON t.FK = t_max.FK AND t.id = t_max.max_id;

方法2：使用窗口函数（MySQL 8.0及以上版本） #

通过ROW_NUMBER()窗口函数按FK分组，按id降序排序后取每组第一行：

SELECT id, merchant, eta, FK
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY FK ORDER BY id DESC) AS rn
    FROM test
) t
WHERE rn = 1;

方法3：自连接筛选 #

通过自连接，筛选出同FK下不存在更大id的记录：

SELECT t1.*
FROM test t1
LEFT JOIN test t2 ON t1.FK = t2.FK AND t2.id > t1.id
WHERE t2.id IS NULL;

以上三种方法均能得到符合预期的结果。

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