如何关联frm_POST表查询frm_THREAD表各主题最新活跃行并按回帖时间排序

• 你的原SQL存在三个核心问题：
  1. JOIN条件语法错误：ON t.ThreadID = .threadID 缺少frm_POST表的别名，应该修改为 ON t.ThreadID = p.threadID
  2. 直接关联会返回主题下所有回帖数据：一个主题对应多条回帖时就会生成冗余行，不符合每个主题仅返回1条最新活跃数据的需求
  3. 排序逻辑错误：默认的ASC升序会把最早的回帖排在最前面，和你要最新活跃优先的需求相反

方案1：MySQL 8.0及以上 / 支持窗口函数的数据库（推荐） #

用ROW_NUMBER()窗口函数给每个主题下的回帖按回帖时间倒序编号，取编号为1的就是每个主题最新的回帖数据：

SELECT ThreadID, isTITLE, postID, isACTIVE, dateCREATED
FROM (
    SELECT
        t.ThreadID, t.isTITLE,
        p.postID, p.isACTIVE, p.dateCREATED,
        -- 按主题分组，回帖时间倒序编号
        ROW_NUMBER() OVER (PARTITION BY t.ThreadID ORDER BY p.dateCREATED DESC) AS rn
    FROM frm_THREAD AS t
    INNER JOIN frm_POST AS p ON t.ThreadID = p.threadID
) AS tmp
WHERE rn = 1 -- 仅取每个主题最新的1条回帖
ORDER BY dateCREATED DESC; -- 所有主题按最新回帖时间倒序排列

方案2：MySQL 5.x 低版本无窗口函数的场景 #

先通过子查询拿到每个主题的最新回帖时间，再关联两张表取完整数据：

SELECT
    t.ThreadID, t.isTITLE,
    p.postID, p.isACTIVE, p.dateCREATED
FROM frm_THREAD AS t
INNER JOIN frm_POST AS p ON t.ThreadID = p.threadID
-- 匹配当前回帖是对应主题的最新回帖
INNER JOIN (
    SELECT threadID, MAX(dateCREATED) AS latest_post_time
    FROM frm_POST
    GROUP BY threadID
) AS latest_p ON p.threadID = latest_p.threadID AND p.dateCREATED = latest_p.latest_post_time
ORDER BY p.dateCREATED DESC;

执行上述SQL后，每个主题仅返回1条对应最新回帖的数据，1分钟前有回复的「Cookies」主题会自动排在2天前有回复的「Pie」主题之前，完全符合需求。

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