在聊天对话中选择最后一行

Question

我有一个像这样的表：

+---+--------+----------+---------+
|id | sender | receiver | created |
+---+--------+----------+---------+
  1     4         9       3333
  2     9         4       3334
  3     4         9       3335
  4     5         4       3336
  5     4         9       3337

我的问题是：

SELECT *
FROM chat_messages 
WHERE sender = 4 OR receiver = 4
GROUP BY sender, receiver
ORDER BY created DESC

结果如下：

+---+--------+----------+---------+
|id | sender | receiver | created |
+---+--------+----------+---------+
  5     4         9       3337
  4     5         4       3336
  2     9         4       3334

我更接近我所寻找的东西了。我正在寻找这个结果：

+---+--------+----------+---------+
|id | sender | receiver | created |
+---+--------+----------+---------+
  5     4         9       3337
  4     5         4       3336

意思是如果对话是用户 n 的，则希望得到最新的行，否则希望得到用户 n 是 发送者 或 接收者 的其他行。提前致谢。

- A. K. M. Tariqul Islam

---

从描述来看，你想要所有的行吗？！ - Strawberry

是的，在涉及用户“n”（无论是作为发送方还是接收方）的情况下。 - A. K. M. Tariqul Islam

但这并不是你在结果集中所指示的。 - Strawberry

我认为它是这样的。在我的结果集中，我有属于用户n的所有行，只有对话的最后一个事件。用户4与9和5交谈，我有与9的最后一个事件和唯一的与5的事件。 - A. K. M. Tariqul Islam

也许我们对“和其他行”有不同的解释。 - Strawberry

Answer 1

你可以在where子句中使用过滤器。以下是一种方法：

select cm.*
from chat_messages cm
where cm.created = (select max(cm2.created)
                    from chat_messages cm2
                    where (cm2.sender, cm2.receiver) in ( (cm.sender, cm.receiver), (cm.receiver, cm.sender))
                   ) and
      4 in (cm.receiver, cm.sender);

如果您只需要创建时间和ID，您可能会发现聚合更好：

select least(cm.receiver, cm.sender),
       greatest(cm.receiver, cm.sender),
       max(cm.id), max(cm.created)
from chat_message cm
where 4 in (cm.receiver, cm.sender)
group by least(cm.receiver, cm.sender), greatest(cm.receiver, cm.sender);

- Gordon Linoff