mysql – 如何分组SQL结果

我在数据库中有以下数据

MAILFROM,MAILTO,TIMESTAMP,MESSAGE
A B   2013-07-01 12:11:12,Hi
B A   2013-07-01 12:12:12,Hi back
A B   2013-07-01 12:13:12,How are you
A C   2013-07-01 12:14:12,Hi there
D A   2013-07-01 12:16:12,Hi
C D   2013-07-01 12:17:12,Hi

如何将其与select组合,以便我得到

C’评论发生3次

SELECT MAILFROM,MAILTO FROM messages WHERE 'A' IN(FROM,TO) GROUP BY FROM

给

一个C.
以及
C A但我希望组合在一起.

它只显示A C 3次

该示例是一个邮箱.

这包含：

MAILFROM,Hi

SQL列表应该列出这个(唯一的对话)

B   2013-07-01 12:13:12,"Hi"  ' Remark Timestap of the latest message
C   2013-07-01 12:14:12,"Hi there"
D   2013-07-01 12:16:12,"Hi"
C D   2013-07-01 12:17:12,"Hi" ' THIS SHOULD NOT BE SHOWN

这意味着这个sql将列出他作为发送者和接收者(从,到)的消息.它应该只列在这个人和发送给谁的人之间,无论谁是MAILFROM或MAILTO.时间戳是他们之间最新消息的日期…备注他永远不会发送到D,无论如何都列出了一个,但是他发送但没有得到任何东西…… B之间是3条消息.所以输出应该只有这3行.. 最佳答案 许多数据库支持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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!