现在有一张短信表pms,有4个字段fromid(发送人id),toid(接收人id),posttime(发送时间),content(内容)
fromid toid posttime content
10001 10002 2010-2-1 aaaaa
10002 10001 2010-2-2 bbbbb
10001 10002 2010-2-3 ccccc
10001 10003 2010-2-1 ddddd
10003 10001 2010-2-2 eeeee
10004 10005 2010-2-2 fffff
现在我要获取与10001的有联系某个所有的短信数量和最后一条短信内容、时间、发送人和接收人(按posttime排序)
即查询结果
count content fromid toid posttime
3 ccccc 10001 10002 2010-2-3
2 eeeee 10003 10001 2010-2-2
请高手指点
fromid toid posttime content
10001 10002 2010-2-1 aaaaa
10002 10001 2010-2-2 bbbbb
10001 10002 2010-2-3 ccccc
10001 10003 2010-2-1 ddddd
10003 10001 2010-2-2 eeeee
10004 10005 2010-2-2 fffff
现在我要获取与10001的有联系某个所有的短信数量和最后一条短信内容、时间、发送人和接收人(按posttime排序)
即查询结果
count content fromid toid posttime
3 ccccc 10001 10002 2010-2-3
2 eeeee 10003 10001 2010-2-2
请高手指点
union
select t1.cnt, t.content, t.fromid, t.toid, t1.posttime from t,(select count(1) cnt, max(posttime), 0 flag from t where toid=10001) t1 where t.toid=10001 and t.posttime=t1.posttime and rownum = 1
select '10001' fromid,'10002' toid,date'2010-2-1' posttime,'aaaaa' content from dual union all
select '10002' fromid,'10001' toid,date'2010-2-2' posttime,'bbbbb' content from dual union all
select '10001' fromid,'10002' toid,date'2010-2-3' posttime,'ccccc' content from dual union all
select '10001' fromid,'10003' toid,date'2010-2-1' posttime,'ddddd' content from dual union all
select '10003' fromid,'10001' toid,date'2010-2-2' posttime,'eeeee' content from dual union all
select '10004' fromid,'10005' toid,date'2010-2-2' posttime,'fffff' content from dual)select count(1)counts,
max(content)keep(dense_rank last order by posttime)content,
max(fromid)fromid,
max(toid)keep(dense_rank last order by posttime)toid,
max(posttime)posttime
from tt where fromid='10001'
union all
select count(1)counts,
max(content)keep(dense_rank last order by posttime)content,
max(fromid)keep(dense_rank last order by posttime)fromid,
max(toid),max(posttime)posttime
from tt where toid='10001'
max(content)keep(dense_rank last order by posttime)content,
max(fromid)fromid,
max(toid)keep(dense_rank last order by posttime)toid,
max(posttime)posttime
from tt where fromid='10001'
union all
select count(1)counts,
max(content)keep(dense_rank last order by posttime)content,
max(fromid)keep(dense_rank last order by posttime)fromid,
max(toid),max(posttime)posttime
from tt where toid='10001'
发现union all后半部分少个字段
新发现个keep函数,呵呵