看到这个功能,觉得有点意思,就动手建表写 sql,花了不少时间,已经正常返回结果:
获取步骤:
1)查询每个不同接收者最新的一条数据;
2)查询每个不同发送者最新的一条数据;
3)查询接收者 /发送者最新的一条数据,并将非本人 id 存放到 other_id;
4)去除重复的 other_id;
5)获取每一条完整的数据,并进行倒序,分页
使用的是 mysql:
select
m1.id,m1.receive_id,m1.send_id,m1.content,m1.ctime from message as m1
join
(
select a.*,max(a.max_ctime) as a_ctime from (
(select receive_id as other_id, receive_id,send_id,max(ctime) as max_ctime from message where receive_id<>'userid' and send_id='userid' group by receive_id )
union
(select send_id as other_id, receive_id,send_id,max(ctime) as max_ctime from message where send_id<>'userid' and receive_id='userid' group by send_id )
) as a group by a.other_id
) as m2
on m1.receive_id=m2.receive_id and m1.send_id=m2.send_id and m1.ctime=m2.a_ctime
order by m1.ctime desc limit 0,20;