无法在 max 函数上聚合 [英] Can't aggregate on max function
本文介绍了无法在 max 函数上聚合的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一张桌子
CREATE TABLE `messages` ( `uid` BIGINT NOT NULL ,
`mid` BIGINT , `date` BIGINT NOT NULL , PRIMARY KEY (`mid`));
我想选择按 uid 分组的 max(date),即对于每个 uid(read user),我想找到最新消息(具有最大日期)
I want to select max(date) grouped by uid, i.e. for every uid(read user) I want to find the latest message (with tha maximum date)
试过了
select messages.mid, max(messages.date), messages.uid, messages.body
from messages
where messages.chat_id is NULL
group by messages.uid
但是查询出错了.
推荐答案
子查询可以为您提供所需的日期,以便为每个用户检索最新消息:
A subquery can give you the date you need in order to retrieve the newest message for each user:
SELECT messages.uid, messages.mid, messages.date, messages.body
FROM messages
WHERE messages.chat_id IS NULL
AND messages.date IN
( SELECT MAX(m2.date) FROM messages m2
WHERE m2.uid = messages.uid AND m2.chat_id IS NULL
)
;
这篇关于无法在 max 函数上聚合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文