V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
qwertyzzz
V2EX  ›  程序员

问个 SQL 查询语句问题

  •  
  •   qwertyzzz ·
    123 · 2018-10-08 18:18:55 +08:00 · 2315 次点击
    这是一个创建于 2236 天前的主题,其中的信息可能已经有所发展或是发生改变。

    实在想不到怎么写了 0 0

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

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

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

    简单来说加个会话表,用这个会话表排序分页,message 表关联到这个会话表去,每次有新消息了更新下会话表的时间
    luguhu
        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;
    ````
    qwertyzzz
        5
    qwertyzzz  
    OP
       2018-10-08 19:01:15 +08:00
    @refear99 噢!这样好像是简单点 那感觉也不需要关联呀 直接会话表存那个好了。
    qwertyzzz
        6
    qwertyzzz  
    OP
       2018-10-08 19:02:15 +08:00
    @luguhu 哇 好复杂 我也不太懂 我来测试测试
    qwertyzzz
        7
    qwertyzzz  
    OP
       2018-10-08 19:05:36 +08:00
    @luguhu 好像不对
    reus
        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
    luguhu
        9
    luguhu  
       2018-10-08 19:28:25 +08:00
    @qwertyzzz 应该是我理解错了, 你是想获取每个对话的第一条吗?
    zhangZMZ
        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 ]
    wanganjun
        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
    beginor
        12
    beginor  
       2018-10-08 22:28:26 +08:00 via Android
    感觉 row_number over partition by 很容易就搞定了, 没有 row number 就难搞了
    alcarl
        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 网页
    关于分页性能问题。既然都要在消息列表里找了,还管那些干啥。。。。。。。。
    ebony0319
        14
    ebony0319  
       2018-10-09 08:55:28 +08:00 via Android
    其实应该用关联子查询。
    james2013
        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;
    qwertyzzz
        16
    qwertyzzz  
    OP
       2018-10-10 21:12:26 +08:00
    @james2013 哇 感觉好复杂的样子 我按楼上的加了一个表 然后发信息的时候更新 倒是可以了。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1190 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 23:08 · PVG 07:08 · LAX 15:08 · JFK 18:08
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.