问个 SQL 查询语句问题

2018-10-08 18:18:55 +08:00
 qwertyzzz

实在想不到怎么写了 0 0

给定某个用户 userid, 如何查询出我的消息列表的记录按照最近的一条交互数据时间倒叙,还有分页。。感觉好难。。

send_id 发送的 id receive_id 接收的 id 假设这个表叫 message

2319 次点击
所在节点    程序员
16 条回复
qwertyzzz
2018-10-08 18:23:09 +08:00
不是那种点击进入聊天界面那种获取聊天记录 是相当于我的聊天信息那种 排列一堆和我聊过天的人的列表
jswh
2018-10-08 18:35:44 +08:00
mark 一下看答案。只用一条 sql......
refear99
2018-10-08 18:56:35 +08:00
首先假设你这个需求是做一个类似论坛的消息中心,不是实时会话 IM,而且必须存数据库

简单来说加个会话表,用这个会话表排序分页,message 表关联到这个会话表去,每次有新消息了更新下会话表的时间
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;
````
qwertyzzz
2018-10-08 19:01:15 +08:00
@refear99 噢!这样好像是简单点 那感觉也不需要关联呀 直接会话表存那个好了。
qwertyzzz
2018-10-08 19:02:15 +08:00
@luguhu 哇 好复杂 我也不太懂 我来测试测试
qwertyzzz
2018-10-08 19:05:36 +08:00
@luguhu 好像不对
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
luguhu
2018-10-08 19:28:25 +08:00
@qwertyzzz 应该是我理解错了, 你是想获取每个对话的第一条吗?
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 ]
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
beginor
2018-10-08 22:28:26 +08:00
感觉 row_number over partition by 很容易就搞定了, 没有 row number 就难搞了
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 网页
关于分页性能问题。既然都要在消息列表里找了,还管那些干啥。。。。。。。。
ebony0319
2018-10-09 08:55:28 +08:00
其实应该用关联子查询。
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;
qwertyzzz
2018-10-10 21:12:26 +08:00
@james2013 哇 感觉好复杂的样子 我按楼上的加了一个表 然后发信息的时候更新 倒是可以了。

这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/495688

V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX