基于论坛ID查询主题并按置顶、最后发帖时间排序的SQL问题

嘿，我看了你的SQL语句，问题主要出在JOIN方向、GROUP BY逻辑以及排序规则这几个地方，导致结果不符合预期。咱们一步步来修正：

先说说你的原SQL哪里不对 #

• JOIN方向搞反了：你用forum_posts LEFT JOIN forum_topics，这会直接过滤掉没有任何帖子的主题——毕竟左表是帖子，没帖子的主题根本匹配不上。但你要的是指定论坛的所有主题，得把forum_topics当左表才行。
• GROUP BY的坑：直接按主题ID分组后，forum_posts.posted和posted_by并不会自动取最后一条帖子的内容，在严格SQL模式下这甚至会报错，因为非聚合列没在GROUP BY里，而且取到的大概率是随机的一条帖子，不是最新的。
• 排序漏了置顶规则：你只按帖子时间降序，完全没考虑sticky（置顶），难怪置顶主题没优先显示。

修正后的SQL（兼容大部分数据库） #

SELECT 
    fp.posted_by, 
    fp.posted, 
    ft.id, 
    ft.subject, 
    ft.sticky, 
    ft.closed
FROM forum_topics ft
LEFT JOIN (
    -- 先找出每个主题的最后一条帖子
    SELECT 
        topic_id, 
        posted_by, 
        posted
    FROM forum_posts
    WHERE (topic_id, posted) IN (
        SELECT topic_id, MAX(posted) 
        FROM forum_posts 
        GROUP BY topic_id
    )
) fp ON ft.id = fp.topic_id
WHERE ft.forum_id = $forumdata->id
-- 先排置顶（sticky值高的在前，一般1是置顶，0是普通），再排最后发帖时间
ORDER BY ft.sticky DESC, COALESCE(fp.posted, ft.created) DESC;

为啥这么写？ #

• 保留所有主题：用forum_topics左连帖子子查询，就算是刚创建还没人回复的主题，也会出现在结果里。
• 精准拿到最后一条帖子：内层子查询先找出每个主题的最新发帖时间，再关联到对应的帖子，确保拿到的是每个主题的最后一条回复。
• 排序符合需求：
  1. 先按sticky降序，置顶主题直接排前面；
  2. 然后用COALESCE处理无帖子的主题——如果没有帖子，就用主题的创建时间（假设你的主题表有created字段，要是没有可以换成主题的其他时间字段，或者直接写fp.posted，这样无帖子的会排在最后）来排序。

进阶写法（支持窗口函数的数据库，比如MySQL8+、PostgreSQL） #

如果你的数据库支持窗口函数，这个写法更清晰，扩展性也更强（要加更多帖子字段直接加就行）：

SELECT 
    fp.posted_by, 
    fp.posted, 
    ft.id, 
    ft.subject, 
    ft.sticky, 
    ft.closed
FROM forum_topics ft
LEFT JOIN (
    SELECT 
        topic_id, 
        posted_by, 
        posted,
        -- 按主题分组，发帖时间降序排，取第1条就是最后一条帖子
        ROW_NUMBER() OVER (PARTITION BY topic_id ORDER BY posted DESC) AS rn
    FROM forum_posts
) fp ON ft.id = fp.topic_id AND fp.rn = 1
WHERE ft.forum_id = $forumdata->id
ORDER BY ft.sticky DESC, COALESCE(fp.posted, ft.created) DESC;

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