gpt4 book ai didi

mysql - 分组时的问题

转载 作者:行者123 更新时间:2023-11-29 05:42:08 27 4
gpt4 key购买 nike

我有这个 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

输出如下:

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

现在,我想使用 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(该分组的最大日期)。但我也从这种行为中获取该行的对应用户(在示例pierre 中)。

但它需要另一个。

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

编辑

事实上,我应该将这个分组应用于整个查询:

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

最佳答案

试试这个(让我知道它是否有效):

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

这是第二个选项:

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

关于mysql - 分组时的问题,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/5917876/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com