mysql表排序不正确，有group-by和order-by

Question

table 1: forum_threads
+-----+------+-------+
| id  | title| status| 
+-----+------+-------+
| 1   |  a   |    1  | 
| 2   |  b   |    1  |
| 3   |  c   |    1  |
| 4   |  d   |    1  |
| 5   |  e   |    1  |
| 6   |  f   |    1  |
+-----+------+-------+
table 2: forum_comments
+-----+----------+--------------------+
| id  | thread_id|         comment    |          
+-----+----------+--------------------+
| 1   |    4     |   hai              | 
| 2   |    4     |   hello            | 
| 3   |    2     |   welcome          | 
| 4   |    2     |   whats your name  | 
| 5   |    6     |   how are you      |
| 6   |    5     |   how old are you  | 
| 7   |    5     |   good             |  
+-----+----------+--------------------+

想要输出

+-----------+----------+-----------------+
| thread_id | title    |  comment_count  |          
+-----------+----------+-----------------+
|    5      |    e     |       2         |
|    6      |    f     |       1         |
|    2      |    b     |       2         | 
|    4      |    d     |       2         |   
+-----------+----------+-----------------+

我的查询

SELECT forum_threads.*,forum_comments.*,count(forum_comments.id) as comment_count 
FROM forum_comments
LEFT JOIN forum_threads ON forum_comments.thread_id = forum_threads.id 
GROUP BY forum_threads.id 
ORDER BY forum_comments.id desc

在这里，我试图通过最新的评论来获得标题。当我给ORDER BY forum_comments.id时，这会返回错误的顺序。我需要根据forum_comments表中的最新评论进行订购。这个查询返回了错误的订单。请帮我找出正确的订单。我怎么能轻松解决这个问题？

Answer 1

这个查询应该会给出预期的结果：

select  t2.thread_id, t1.title, t2.comment_count from forum_threads as t1,
(SELECT id, thread_id, count(comment) as comment_count from forum_comments group by thread_id) as t2
where t1.id = t2.thread_id order by t2.id desc;

Answer 2

您可以不使用forum_threads.*和forum_comments.*来指定具体的列名并尝试。

如果这不起作用，您应该尝试显式分配主键和外键。