MySQL多对多关系中按name分组获取各玩家最新帖子的问题

表结构说明 #

现有三个MySQL表：posts、players及关联表players_posts_links，posts与players为多对多关系，且players.name字段唯一。具体表数据如下：

posts表 #

players表 #

players_posts_links表 #

问题描述 #

需要查询每个玩家关联的最新帖子，但执行以下SQL时，无法始终返回正确的最新帖子：

SELECT players.name, any_value(posts.title)
FROM posts
         LEFT JOIN players_posts_links link on posts.id = link.post_id
         LEFT JOIN players on link.player_id = players.id
GROUP BY players.name
ORDER BY max(posts.timestamp) DESC

预期结果：

问题原因 #

原查询使用any_value(posts.title)，该函数会从分组后的记录中随机选取一个值，无法保证它对应max(posts.timestamp)的那条帖子。当玩家关联多条帖子时，返回的标题可能不是最新时间对应的内容。

解决方案 #

方法1：窗口函数（MySQL 8.0+推荐） #

利用ROW_NUMBER()窗口函数，按玩家分组后给每条帖子按时间倒序排号，取排号为1的记录（即最新帖子）：

SELECT 
    p.id AS `posts.id`,
    p.title AS `posts.title`,
    pl.name AS `players.name`
FROM (
    SELECT 
        posts.id,
        posts.title,
        players.name,
        ROW_NUMBER() OVER (PARTITION BY players.name ORDER BY posts.timestamp DESC) AS rn
    FROM posts
    JOIN players_posts_links link ON posts.id = link.post_id
    JOIN players ON link.player_id = players.id
) AS p
WHERE p.rn = 1
ORDER BY posts.timestamp DESC;

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

先查询每个玩家的最新帖子时间，再通过关联匹配对应的帖子信息：

SELECT 
    posts.id AS `posts.id`,
    posts.title AS `posts.title`,
    players.name AS `players.name`
FROM players
JOIN players_posts_links link ON players.id = link.player_id
JOIN posts ON link.post_id = posts.id
JOIN (
    SELECT 
        players.id AS player_id,
        MAX(posts.timestamp) AS latest_time
    FROM players
    JOIN players_posts_links link ON players.id = link.player_id
    JOIN posts ON link.post_id = posts.id
    GROUP BY players.id
) AS latest ON players.id = latest.player_id AND posts.timestamp = latest.latest_time
ORDER BY posts.timestamp DESC;

结果验证 #

两种方法均可返回符合预期的结果，准确匹配每个玩家的最新关联帖子。

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