MySQL-如何显示每个线程的最新主题 [英] MySQL - how to show the latest topic per thread

问题描述

我正在尝试创建SQL来检索论坛主题的最新帖子列表. 我有以下代码:

I am trying to create SQL for retrieveing a list of latests posts for the forum thread. I have the following code:

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只会获得组中的第一行.

Obviously this will group without the respect of if the post is latest or not. item_discuss_post_title will just get the first row in the group.

我想知道是否有解决办法?如果没有，那么解决问题的最佳方法是什么……仅子查询?

I wonder if there's some way around this? If not, what is the best way to solve the problem... only subqueries?

谢谢， 帕维尔

更新: 请注意，我需要所有线程，LIMIT 1不能解决问题. 另外，ORDER BY也不是一个选项，因为GROUP BY仍将从组中选择第一个记录. 这似乎不是一个简单的问题.

UPDATE: Please note that I need all threads, LIMIT 1 is not solving the problem. Also ORDER BY is not an option as GROUP BY will select the first record from group anyway. This is not such a simple question as it can seem to be.

更新:

我真的很想尝试避免使用子查询，或者如果这样做，请尽可能使用它. 我目前所拥有的是这样的:

I really want to try to avoid using subqueries or if doing so - use it the optimal may. What I came with currently is something like this:

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

EXPLAIN EXTENDED给出以下结果:

EXPLAIN EXTENDED gives the following result:

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, 

推荐答案

尝试一下.

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__.

Replace __datecolumn__ with the column that stores posting time.

这篇关于MySQL-如何显示每个线程的最新主题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！