sql - 从表中选择不同的行，其中 2 列具有相同的外键（反向包含）

Question

我有一个包含字段 id、时间、isread、message、messagestatus、receiver 和 user 的消息表。我想检索 id 为 1 的用户发送或接收给任何其他用户的最新消息（基于时间）。

例如，如果 ID 为 1（发送者）的用户向 ID 为 2（接收者）的用户发送消息，如果 ID 为 2 的用户向 ID 为 1 的用户发送消息。只应选择它们之间发送的最新消息

我已经存在的表如下所示：

我试过了SELECT * from (SELECT MAX(time) as time ,sender,receiver from message group by sender,receiver) as f where receiver=1 or sender=1 limit 20

但它只显示发送者、接收者和时间，而省略了其他列，如 message、id、messagestatus 和 read，而且它不查找两个用户之间发送的一般最后一条消息，而是查找 user1 和另一个之间发送的最新消息当 user1 是发送者而另一个用户是接收者时的用户，并且当 user1 是接收者而另一个用户是发送者时，也会找到最后一条消息。

标签： sqlpostgresql

Answer 1

如果您想要用户“1”发送或接收的最新消息，请使用过滤，而不是group by：

select m.*
from messages m
where 1 in (m.sender, m.receiver) and
      m.time = (select max(m2.time)
                from messages m2
                where (m2.sender = m.sender and m2.receiver = m.receiver) or
                      (m2.receiver = m.sender and m2.sender = m.receiver)
               );

如果两条消息可能具有完全相同的时间戳，则：

select m.*
from messages m
where 1 in (m.sender, m.receiver) and
      m.id = (select id
              from messages m2
              where (m2.sender = m.sender and m2.receiver = m.receiver) or
                    (m2.receiver = m.sender and m2.sender = m.receiver)
              order by m2.time desc, m2.id desc
              limit 1
             );