返回具有特定 MAX(字段) 的 group by 后的记录

Question

我搜索了很多这样的主题，但我无法应用到我的查询。 这是：

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, SUM(CASE WHEN r.user IS NULL THEN 1 ELSE 0 END) to_view
FROM forum_categories
JOIN forum_topics ON forum_topics.category_id=forum_categories.id 
LEFT OUTER JOIN (SELECT topic, user FROM forum_visits WHERE user='userA') r ON forum_topics.id=r.topic
JOIN forum_messages ON forum_messages.topic_id=forum_topics.id
GROUP BY forum_categories.id 
ORDER BY forum_categories.date

它有效：唯一的问题是仅返回具有 MAX 数据的字段 forum_messages.date ；相反，我想返回带有 MAX 字段的整行（例如通讯作者）。

所以我应该返回的，简而言之，是：

1. 每个类别的标题； *（目前有效）
2. 该类别的主题数量； （目前有效）
3. 该类别的所有主题的回复数量； （这里还有另一个条件，如您所见，该回复的计数器是通过提交的原始=0的消息获取的）（目前有效）
4. 最后一条消息的作者/数据对于该类别（这里有问题：它仅正确返回日期，而不返回作者）；
5. 一个标志，指示是否有 userA 尚未检查的任何主题； （目前也有效：如果 SUM 返回大于 0 的值，则有一个主题未查看）
6. 该查询应该尽可能快，因为表可能非常大；

有关详细信息，这些是我的实际表格：

CREATE TABLE IF NOT EXISTS `forum_categories` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `title` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `forum_topics` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `category_id` int(11) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `author` varchar(255) NOT NULL,
  `date` datetime NOT NULL,
  `view` int(11) unsigned NOT NULL default '0',
  `sticky` tinyint(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;    

CREATE TABLE IF NOT EXISTS `forum_messages` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `topic_id` int(11) unsigned NOT NULL,
  `author` varchar(255) NOT NULL,
  `message` mediumtext NOT NULL,
  `date` datetime NOT NULL,
  `original` tinyint(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `forum_visits` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `topic` int(11) unsigned NOT NULL,
  `user` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `forum_visits_unique_idx` (`topic`,`user`)
) ENGINE=MyISAM AUTO_INCREMENT=131 DEFAULT CHARSET=utf8;

希望有人可以帮助我！

Answer 1

我们可以使用您上一个问题中的查询来获取消息作者和消息日期，并使用上面的查询来获取计数器（主题和回复），并将它们连接在一起：

编辑：此查询有效（已测试）。但它有点复杂，并且有 2 个子查询，所以我希望其他人发布一个更好、更简单的子查询。如果数据库变得非常大，这个数据库可能会变得缓慢。

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,
t2.author, t2.last_message

-- first get the counters per category
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

-- Then join a query to get last message per category
JOIN (SELECT forum_categories.id, 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
          GROUP BY forum_categories.id) t2
     ON t2.id = forum_categories.id

GROUP BY forum_categories.id

Answer 2

为了用丢失的数据补充当前的输出，我可能会这样：

SELECT
  forum_stats.*,   /* just repeat the already pulled columns (expand it if needed) */
  forum_messages.* /* and here you may actually want to be more specific as to
                       what else you would like to pull from forum_messages */
FROM (
  SELECT
    forum_categories.id AS category_id,
    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,
    MAX(forum_messages.date) AS last_message,
    SUM(CASE WHEN r.user IS NULL THEN 1 ELSE 0 END) AS to_view,
    forum_categories.date
  FROM forum_categories
    JOIN forum_topics ON forum_topics.category_id=forum_categories.id 
    LEFT OUTER JOIN (
      SELECT topic, user FROM forum_visits WHERE user='userA'
    ) r ON forum_topics.id=r.topic
    JOIN forum_messages ON forum_messages.topic_id=forum_topics.id
  GROUP BY forum_categories.id
) forum_stats
  JOIN forum_topics ON forum_topics.category_id=forum_stats.category_id
  JOIN forum_messages ON forum_messages.topic_id=forum_topics.id
    AND forum_messages.date=forum_stats.last_message
ORDER BY forum_stats.date

当然，这假设 forum_messages.date 不仅仅是一个日期，而是一个时间戳，并且没有两条消息可以绝对共享相同的时间戳。