我正在尝试获取

  • 最新线程 (id, topic, timestamp, author_id) 和
  • 最新帖子 (id, thread_id, timestamp, author_id)
  • 每个论坛的
  • (id, name)
  • 由最新帖子 订购,独立于线程的创建日期。

  • 为什么?

    我希望能够显示以下详细信息:
    "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 以获得尽可能高的性能所需的结果?我试图避免子查询,但我思想开放!

    提前致谢!

    最佳答案

    由于 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
    

    关于mysql - SQL:选择最新主题和最新帖子,按论坛分组,按最新帖子排序,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/17222233/

    10-13 08:37