实在想不到怎么写了 0 0
给定某个用户 userid, 如何查询出我的消息列表的记录按照最近的一条交互数据时间倒叙,还有分页。。感觉好难。。
send_id 发送的 id receive_id 接收的 id 假设这个表叫 message
1
qwertyzzz OP 不是那种点击进入聊天界面那种获取聊天记录 是相当于我的聊天信息那种 排列一堆和我聊过天的人的列表
|
2
jswh 2018-10-08 18:35:44 +08:00
mark 一下看答案。只用一条 sql......
|
3
refear99 2018-10-08 18:56:35 +08:00
首先假设你这个需求是做一个类似论坛的消息中心,不是实时会话 IM,而且必须存数据库
简单来说加个会话表,用这个会话表排序分页,message 表关联到这个会话表去,每次有新消息了更新下会话表的时间 |
4
luguhu 2018-10-08 18:57:09 +08:00
不是很明白什么意思, 这样么?
````sql select a.receive_id, a.send_id, a.is_read, a.ctime from message a join (select receive_id, send_id from message where (receive_id = userid or send_id = userid) order by ctime desc limit 1) b on b.receive_id = a.receive_id and b.send_id = a.send_id order by ctime desc; ```` |
8
reus 2018-10-08 19:11:00 +08:00
select * from message
order by ( select max(ctime) from message m2 where message.send_id = m2.send_id ) desc |
10
zhangZMZ 2018-10-08 19:40:11 +08:00
没懂,是不是这样?
SELECT * FROM message WHERE `receive_id` =:id OR `send_id` =:id ORDER BY `ctime` DESC limit :page,:count; [ 'id'=>id,'page'=>page,'count'=>count ] |
11
wanganjun 2018-10-08 22:12:20 +08:00
用 window 函数为每一行编号就好做了
select * from ( select receive_id, send_id, is_read, ctime from ( select *, row_number() over(PARTITION BY receive_id, send_id order by ctime desc) from ( select * from message where receive_id = <userId> or send_id = <userId> ) as t ) as t where row_number = 1 ) as t limit 20 |
12
beginor 2018-10-08 22:28:26 +08:00 via Android
感觉 row_number over partition by 很容易就搞定了, 没有 row number 就难搞了
|
13
alcarl 2018-10-08 22:47:17 +08:00
select myid,uid,max(ctime+is_read+soRr) as data
from ( select send_id,receive_id,is_read,ctime,'send' as sORr where send_id='myid' union all select receive_id,send_id,is_read,ctime,'recv' as sORr where receive_id='myid' ) group by myid,uid order by data desc 。。。。。这样写是不是很皮,难得我还特意登一下 PC 网页 关于分页性能问题。既然都要在消息列表里找了,还管那些干啥。。。。。。。。 |
14
ebony0319 2018-10-09 08:55:28 +08:00 via Android
其实应该用关联子查询。
|
15
james2013 2018-10-10 16:35:36 +08:00
看到这个功能,觉得有点意思,就动手建表写 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; |