php - 一组 2 个用户的 SQL 查询

Question

我的数据库中有一个表保存用户之间的消息。

表“dt_inbox”具有以下结构:
- 消息ID(PK)
- fromid => 由 userid=A 发起的消息
- toid => 消息发送至 userid=B
-conversationid => 对话的id
- datesent => 消息发送日期
- inbox_view => 标记 0 或 1 以了解消息是否已被阅读
- inboxdelete => 标记 0 或 1 以了解邮件是否已被删除

我正在尝试创建一个查询，以获得用户 ID A 的所有消息的列表:- 由用户 A 发送给用户 B(C,D,E...) ，或由用户 B (C,D,E...) 发送给用户 A => 消息从 id A 发送 -> toid B 或 fromid B -> toid A 应出现在一行中。
- 每对用户 fromid/toid 应出现在一行中。
- 显示用户 A 和 B 之间最后一条消息的最大日期(datesent)
- 按 datemax 排序
- inboxdeleted = 0 和 inbox_view=0。

这是我的查询，但结果与预期不符，对于 userid = 12 :

SELECT messageid, conversationid, fromid, toid,
                (SELECT MAX(datesent) FROM dt_inbox dd WHERE dd.conversationid =     d.conversationid) as datemax,
                (SELECT count(*) FROM dt_inbox dd WHERE dd.conversationid=d.conversationid) as messagesnr,
                (SELECT count(*) FROM dt_inbox dd WHERE dd.conversationid=d.conversationid AND dd.inboxview=0 AND toid=12) as unreadsrn
            FROM dt_inbox d
            WHERE (toid = 12 AND inboxdelete=0
            OR fromid = 12 AND inboxdelete=0)
            GROUP BY conversationid
            ORDER BY datemax DESC

这给出了以下结果:

messageid  conversationid  fromid  toid  datemax  messagesnr  unreadsrn
     1239            2139    12     159   date       1             0    
     1238            2138    12      22   date       1             1    
     1237            2137    12     159   date       1             0    
     1236            2136    12      22   date       1             0
     1235            2135    159     12   date       1             1    
     1234            2134    159     12   date       1             1        

在此示例中，对于 userid=12，我只想获取该用户与所有其他用户之间的最后消息，如果消息来自 id 或 toid 12，则不做任何区别

我希望我的结果为:

messageid  conversationid  user1  user2  direction  datemax  messagesnr  unreadsrn
     1239            2139    12     159   from        date       1           0    
     1238            2138    12      22    to         date       1           1    

新列“方向”将是一个标志，显示最后一条消息是来自还是发往 userid=12。

提前感谢您的帮助，我真的被阻止了:(

Answer 1

没试过;-)但也许这是正确的方法。

SELECT max(messageid) as messageid, conversationid, fromid, toid,
       datemax, messagesnr, unreadsrn, toid as tmp_toid
    FROM dt_inbox
    WHERE fromid=12
    GROUP BY toid
JOIN
SELECT max(messageid) as messageid, conversationid, fromid, toid,
       datemax, messagesnr, unreadsrn, fromid as tmp_fromid
    FROM dt_inbox
    WHERE toid=12
    GROUP BY fromid
ON tmp_toid=tmp_fromid