SQL：选择最新主题和最新帖子，按论坛分组，按最新帖子排序

Question

我正在尝试

• 最新线程 （ID，主题，时间戳，author_id）和
• 最新帖子 （id，thread_id，时间戳，author_id）
• 每个论坛 （ID，姓名）
• 由最新帖子排序 ，与该线程的创建日期无关。

为什么？

我希望能够显示以下详细信息：

"The latest Answer of forum $forum_id was given on Question $thread_id. Here it is: $post_id"

SELECT  f.id AS forum_id,
        f.name AS forum_name,
        t.id AS thread_id,
        t.topic AS thread_topic,
        t.ts AS thread_timestamp,
        p.id AS post_id,
        p.content AS post_content,
        p.ts AS post_timestamp

 FROM   forums f,
        threads t,
        posts p

WHERE   f.id = t.forum_id 
  AND   t.id = p.thread_id

GROUP BY f.id
ORDER BY p.ts

有什么建议，如何更改SQL以尽可能获得所需的结果？我试图避免子查询，但我思想开放！

提前致谢！

Answer 1

由于MySQL不支持窗口函数，因此我认为没有子查询就无法实现这一点：

SELECT  f.id AS forum_id,
    f.name AS forum_name,
    t.id AS thread_id,
    t.topic AS thread_topic,
    t.ts AS thread_timestamp,
    p.id AS post_id,
    p.content AS post_content,
    p.ts AS post_timestamp

FROM   forums f
JOIN (SELECT t2.forum_id, max(p2.ts) as ts
      FROM posts p2
      JOIN threads t2 ON p2.thread_id = t2.id
      GROUP BY t2.forum_id) max_p ON f.id = max_p.forum_id
JOIN   posts p ON max_p.ts = p.ts
JOIN   threads t ON f.id = t.forum_id AND p.thread_id = t.id
ORDER BY p.ts

自然地，缓存最新结果将使您无需调用MAX（）即可降低性能，但是有了正确的索引，这应该不是什么大问题了…

更新

包括无帖子的线程和无主题的论坛的最简洁的方法是使用LEFT JOIN而不是INNER JOIN：

SELECT  f.id AS forum_id,
    f.name AS forum_name,
    t.id AS thread_id,
    t.topic AS thread_topic,
    t.ts AS thread_timestamp,
    p.id AS post_id,
    p.content AS post_content,
    p.ts AS post_timestamp

FROM   forums f
LEFT JOIN (SELECT t2.forum_id, max(COALESCE(p2.ts, t2.ts)) as ts, COUNT(p2.ts) as post_count
      FROM threads t2 
      LEFT JOIN posts p2 ON p2.thread_id = t2.id
      GROUP BY t2.forum_id) max_p ON f.id = max_p.forum_id
LEFT JOIN   posts p ON max_p.ts = p.ts
LEFT JOIN   threads t ON f.id = t.forum_id AND (max_p.post_count = 0 OR p.thread_id = t.id)
ORDER BY p.ts