mysql sql 分组_mysql – 如何分组SQL结果

许多数据库支持least()和most()函数.你可以做你想做的事情：

select least("from","to") as party1,greatest("from","to") as party2,count(*) as NumMessages,max(timestamp) as maxtimestamp

from messages

group by least("from","to"),"to") ;

以下使用案例isntead(标准SQL),并且应该在大多数数据库中工作：

select (case when "from" < "to" then "from" else "to" end) as party1,(case when "from" < "to" then "to" else "from" end) as party2,max(timestamp) as maxtimestamp

from messages

group by (case when "from" < "to" then "from" else "to" end),(case when "from" < "to" then "to" else "from" end)

编辑：

如果您希望将此作为给定人员的唯一消息：

select (case when "from" = const.ThePerson then "to" else "from" end) as Other,max(timestamp) as maxtimestamp

from messages m cross join

(select 'A' as ThePerson) const

where const.ThePerson in ("from","to")

group by "from","to";

要获取最后一条消息,您需要加入原始数据：

select Other,NumMessages,MaxTimeStamp,m.message

from (select (case when "from" = const.ThePerson then "to" else "from" end) as Other,max(timestamp) as maxtimestamp,max(ThePerson) as ThePerson,from messages m cross join

(select 'A' as ThePerson) const

where const.ThePerson in ("from","to")

group by "from","to"

) t join

messages m

on m."from" in (t.Other,t.ThePerson) and

m."to" in (t.Other,t.ThePerson) and

m.TimeStamp = t.maxtimestamp