SQL:选择最新主题和最新帖子，按论坛分组，按最新帖子排序 [英] SQL: Select latest thread and latest post, grouped by forum, ordered by latest post

问题描述

我正在尝试获取

• 最新主题(id，主题，时间戳，author_id)和
• 最新帖子(id，thread_id，时间戳，author_id)
每个论坛的
• (ID，名称)
• 由最新帖子排序，与主题的创建日期无关.

• latest thread (id, topic, timestamp, author_id) and
• latest post (id, thread_id, timestamp, author_id)
• of each forum (id, name)
• ordered by the latest post, indepent from the thread's creationdate.

为什么?

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

I'd like to be able to display details like:

"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以获得尽可能多的性能所需的结果?我正在尝试避免子查询，但是我思想开放！

---

Any advices, how to change the SQL to get the wanted result as much performant as possible? I'm trying to avoid subqueries but I'm open-minded!

提前谢谢！

推荐答案

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

Since MySQL doesn't support window functions, I don't think there's any way to do this without a subquery:

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()即可降低性能，但是有了正确的索引，这应该不是什么大问题...

Naturally, caching the latest results would let you do this without the performance penalty of calling MAX(), but with the right indices, this shouldn't be much of an issue...

更新

包括没有帖子的主题和没有主题的论坛的最简洁的方法是使用LEFT JOINs而不是INNER JOINs:

The most concise way of including the threads without posts and forums without threads would be to use LEFT JOINs instead of an INNER JOINs:

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

这篇关于SQL:选择最新主题和最新帖子，按论坛分组，按最新帖子排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！