我在数据库中有以下数据
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