分组时出现问题 [英] Problem when grouping

问题描述

我有这个MySql查询：

  SELECT forum_categories.title，forum_messages.author，forum_messages.date AS last_message 
 FROM forum_categories 
 JOIN forum_topics ON forum_topics.category_id = forum_categories.id 
 JOIN forum_messages ON forum_messages.topic_id = forum_topics.id 
 WHERE forum_categories.id = 6 
 ORDER BY forum_categories.date ASC 
  

输出结果如下：

 欢迎daniel 2010-07-09 22:14:49 
欢迎daniel 2010-06-29 22:14:49 
欢迎luke 2010-08-10 20:12:20 
欢迎来到skywalker 2010-08-19 22:12:20 
欢迎美味2010-10-09 19:12:20 
欢迎daniel 2011-11-05 23： 12:20 
欢迎pierre 2011-11-05 23:12:22 
  

现在，我想用MAX日期对它进行分组。因此查询变成：

  SELECT forum_categories.title，forum_messages.author，forum_messages.date AS last_message，MAX（forum_messages.date） 
 FROM forum_categories 
 JOIN forum_topics ON forum_topics.category_id = forum_categories.id 
 JOIN forum_messages ON forum_messages.topic_id = forum_topics.id 
 WHERE forum_categories.id = 6 
 GROUP BY forum_categories.id 
 ORDER BY forum_categories.date ASC 
  

完美！所采用的日期是正确的： 2011-11-05 23:12:22 （该分组的MAX日期）。但我也从这种行为方面着手为该行获取相应的用户（在 pierre 示例中）。

但需要另一个。

为什么？它不选择整行？我该如何解决这个问题？干杯

编辑

实际上，我应该将这个分组应用到这个整体query：

  SELECT forum_categories.title，COUNT（DISTINCT forum_topics.id）AS total_topics，SUM（CASE WHEN forum_messages.original = 0 （last_messages.date）AS last_message 
 FROM forum_categories 
 JOIN forum_topics ON forum_topics.category_id = forum_categories.id 
在forum_messages上加入forum_messages。 topic_id = forum_topics.id 
 GROUP BY forum_categories.id 
 ORDER BY forum_categories.date 
  

 选择论坛_类别.title，forum_messages.author，
 forum_messages.date AS last_message 
 FROM forum_categories 
 JOIN forum_topics ON forum_topics.category_id = forum_categories.id 
 JOIN forum_messages ON forum_messages.topic_id =论坛_topics.id 
 JOIN（SELECT MAX（m.date）as date，top.category_id 
 FROM forum_messages m 
 JOIN forum_topics top ON m.topic_id = top.id 
 GROUP BY top.category_id）as t 
 ON t.category_id = forum_topics.category_id AND t.date = forum_messages.date 
 WHERE forum_categories.id = 6 
 GROUP BY forum_categories.id 
  

以下是第二个选项：

  SELECT forum_categories.title，forum_messages.author，
 forum_messages.date AS last_message 
 FROM forum_categories 
 JOIN forum_topics ON forum_topics.category_id = forum_categories.id 
 JOIN forum_messages ON forum_messages .topic_id = forum_topics.id 
 WHERE forum_categories.id = 6 
 AND forum_messages.date =（SELECT MAX（date）
 FROM forum_messages t 
 WHERE t.topic_id = forum_topics.id ）
 GROUP BY forum_categories.id 
 ORDER BY forum_categories.date ASC 
  

I have this MySql query :

SELECT forum_categories.title, forum_messages.author, forum_messages.date AS last_message
FROM forum_categories
JOIN forum_topics ON forum_topics.category_id=forum_categories.id 
JOIN forum_messages ON forum_messages.topic_id=forum_topics.id
WHERE forum_categories.id=6
ORDER BY forum_categories.date ASC

And the output is the follow :

Welcome     daniel      2010-07-09 22:14:49
Welcome     daniel      2010-06-29 22:14:49
Welcome     luke        2010-08-10 20:12:20
Welcome     skywalker   2010-08-19 22:12:20
Welcome     delicious   2010-10-09 19:12:20
Welcome     daniel      2011-11-05 23:12:20
Welcome     pierre      2011-11-05 23:12:22

Now, I'd like to group it using the MAX date. So the query become :

SELECT forum_categories.title, forum_messages.author, forum_messages.date AS last_message, MAX(forum_messages.date)
FROM forum_categories
JOIN forum_topics ON forum_topics.category_id=forum_categories.id 
JOIN forum_messages ON forum_messages.topic_id=forum_topics.id
WHERE forum_categories.id=6
GROUP BY forum_categories.id
ORDER BY forum_categories.date ASC

PERFECT! The date taken is correct: 2011-11-05 23:12:22 (the MAX date for that grouping). But I also aspect from this behaviour to get the corrispondent user for that line (in the example pierre).

But it take another one.

Why? It doesnt select the whole row? And how can I fix this trouble? Cheers

EDIT

In fact, I should apply this grouping to this whole query :

SELECT forum_categories.title, COUNT(DISTINCT forum_topics.id) AS total_topics, SUM(CASE WHEN forum_messages.original=0 THEN 1 ELSE 0 END) AS total_replies, forum_messages.author, MAX(forum_messages.date) AS last_message
FROM forum_categories
JOIN forum_topics ON forum_topics.category_id=forum_categories.id 
JOIN forum_messages ON forum_messages.topic_id=forum_topics.id
GROUP BY forum_categories.id 
ORDER BY forum_categories.date

解决方案

Try this one (let me know if it works):

SELECT forum_categories.title, forum_messages.author, 
   forum_messages.date AS last_message
FROM forum_categories
JOIN forum_topics ON forum_topics.category_id=forum_categories.id
JOIN forum_messages ON forum_messages.topic_id=forum_topics.id
JOIN (SELECT MAX(m.date) as date, top.category_id  
     FROM forum_messages m
     JOIN forum_topics top ON m.topic_id = top.id
     GROUP BY top.category_id) as t
  ON t.category_id = forum_topics.category_id AND t.date = forum_messages.date
WHERE forum_categories.id=6
GROUP BY forum_categories.id

Here is a second option:

SELECT forum_categories.title, forum_messages.author, 
       forum_messages.date AS last_message
FROM forum_categories
JOIN forum_topics ON forum_topics.category_id=forum_categories.id
JOIN forum_messages ON forum_messages.topic_id=forum_topics.id
WHERE forum_categories.id=6
AND forum_messages.date = (SELECT MAX(date)
                           FROM forum_messages t
                           WHERE t.topic_id = forum_topics.id)
GROUP BY forum_categories.id
ORDER BY forum_categories.date ASC

这篇关于分组时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！