代码之家 › 专栏 › 技术社区 › Pavel Dubinin

MySQL-如何显示每个线程的最新主题

forum mysql

Pavel Dubinin · 技术社区 · 16 年前

我正在尝试创建SQL,用于检索论坛线程的最新帖子列表。我有以下代码:

SELECT
 item_discuss_thread_id
 , item_discuss_post_title
 , COUNT(item_discuss_thread_id) AS nb_posts
FROM
 item_discuss_posts
GROUP BY
 item_discuss_thread_id

item_discuss_post_title 将只获得组中的第一行。

我想知道是否有办法解决这个问题?如果没有,解决问题的最佳方法是什么。。。只有子查询?

谢谢帕维尔

更新: 请注意,我需要所有线程,限制1不能解决问题。此外,ORDER BY不是一个选项,因为GROUP BY仍将从GROUP中选择第一条记录。这不是一个看似简单的问题。

更新:

我真的想尽量避免使用子查询,或者如果这样做的话——尽可能地使用它。我现在带的是这样的东西:

SELECT
  ordered_by_date.item_discuss_thread_id
  , item_discuss_post_title
  , COUNT(item_discuss_thread_id) AS nb_posts
FROM
  (
   SELECT
     item_discuss_thread_id
     , item_discuss_post_title
   FROM
      item_discuss_posts
   ORDER BY
      item_discuss_post_datetime DESC
  ) AS ordered_by_date
GROUP BY
   item_discuss_thread_id

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, PRIMARY, <derived2>, ALL, \N, \N, \N, \N, 20, Using temporary; Using filesort
2, DERIVED, item_discuss_posts, index, \N, item_discuss_post_datetime, 8, \N, 20,

2 回复 | 直到 16 年前

Dan Loewenherz Snowman 16 年前

试试这个。

SELECT
  *
FROM
  (SELECT item_discuss_thread_id, item_discuss_post_title, COUNT(item_discuss_thread_id) AS nb_posts
   FROM item_discuss_posts
   ORDER BY __datecolumn__)
  AS ordered_by_date
GROUP BY
  ordered_by_date.item_discuss_thread_id

__datecolumn__ 使用存储过帐时间的列。

Pavel Dubinin 16 年前

好的,我自己带着解决方案来的。我使用依赖子查询来解决这个问题。

        SELECT
             item_discuss_threads.item_discuss_thread_id
             , item_discuss_threads.item_discuss_thread_datetime
             , item_discuss_threads.item_discuss_thread_title
             , latest_posts.item_discuss_post_title
             , latest_posts.item_discuss_post_datetime
             , COUNT(item_discuss_posts.item_discuss_post_id) AS nb_posts
        FROM
             item_discuss_threads
        INNER JOIN item_discuss_posts
             ON item_discuss_threads.item_discuss_thread_id=item_discuss_posts.item_discuss_thread_id
        INNER JOIN item_discuss_posts AS latest_posts
             ON latest_posts.item_discuss_thread_id=item_discuss_threads.item_discuss_thread_id
        WHERE
             (
                  SELECT
                        item_discuss_post_id
                  FROM
                        item_discuss_posts AS p
                  WHERE
                        p.item_discuss_thread_id=item_discuss_posts.item_discuss_thread_id
                  ORDER BY
                        item_discuss_post_datetime DESC
                  LIMIT
                       1
             )=latest_posts.item_discuss_post_id
        GROUP BY
             item_discuss_threads.item_discuss_thread_id
        ORDER BY
            latest_posts.item_discuss_post_datetime DESC