如何用单条SQL查询展示5个含最新回复的主题（仅显最新回复）

需求说明 #

查询展示最多5个主题，每个主题仅显示其最新的回复，并且按主题的最新回复时间倒序排列（最新回复的主题排在最前）。

表结构 #

quests（主题表） #

+----+----------------+--------------------------------+------------+
| id | name           | text                           | date       |
+----+----------------+--------------------------------+------------+
|  1 | Quest 1        | Quest 1 Text                   | 1702570100 |
+----+----------------+--------------------------------+------------+
|  2 | Quest 2        | Quest 2 Text                   | 1702570120 |
+----+----------------+--------------------------------+------------+

answers（回复表） #

+----+-----------+-----------------------------+------------+
| id | quest_id  | text                        | date       |
+----+-----------+-----------------------------+------------+
|  1 |         1 | Answer 1                    | 1702570135 |
+----+-----------+-----------------------------+------------+
|  2 |         1 | Answer 2                    | 1702570136 |
+----+-----------+-----------------------------+------------+
|  3 |         2 | Answer 1                    | 1702570137 |
+----+-----------+-----------------------------+------------+
|  4 |         2 | Answer 2                    | 1702570138 |
+----+-----------+-----------------------------+------------+
|  5 |         2 | Answer 3                    | 1702570139 |
+----+-----------+-----------------------------+------------+

现有SQL的问题 #

• 尝试语句1：GROUP BY t1.id未配合聚合或筛选逻辑，MySQL会返回分组内任意一条回复，无法保证是最新的。
• 尝试语句2：子查询的ORDER BY date DESC不会影响后续GROUP BY结果，GROUP BY仍会返回任意匹配的回复，逻辑不成立。

正确SQL实现 #

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

利用ROW_NUMBER()窗口函数对每个主题的回复按时间倒序编号，取编号为1的最新回复：

SELECT q.name, a.text AS latest_answer
FROM quests q
JOIN (
    SELECT 
        quest_id, 
        text,
        date,
        ROW_NUMBER() OVER (PARTITION BY quest_id ORDER BY date DESC) AS rn
    FROM answers
) a ON q.id = a.quest_id AND a.rn = 1
ORDER BY a.date DESC
LIMIT 5;

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

通过子查询获取每个主题的最新回复时间，再匹配对应的回复内容：

SELECT q.name, a.text AS latest_answer
FROM quests q
JOIN answers a ON q.id = a.quest_id
WHERE a.date = (
    SELECT MAX(date) 
    FROM answers 
    WHERE quest_id = q.id
)
ORDER BY a.date DESC
LIMIT 5;

查询结果 #

执行后会得到符合预期的结果：

Quest 2
- Answer 3
Quest 1 
- Answer 2

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