时间戳

首页 > 解决方案 > 按更改时匹配的两个列分组

mysql - 按更改时匹配的两个列分组

问题描述

我在数据库中有一个名为 Message 的表，该表如下所示：

消息表

我需要找到 gomez 与表中每个用户的最后对话消息。因此，我想创建一个将发送者和接收者用户名和分组依据与该列相结合的列。但是，我如何验证

露西+戈麦斯 == 戈麦斯+露西

到目前为止我的代码：

SELECT sender+receiver, content, dateSent
FROM MESSAGE
WHERE sender = 'Gomez' OR receiver = 'Gomez'
GROUP BY sender+receiver 
ORDER BY dateSent desc;

标签： mysqlsqldatabase

解决方案

使用NOT EXISTS代替GROUP BY：

SELECT m1.*
FROM MESSAGE m1
WHERE 'Gomez' IN (m1.sender, m1.receiver) 
AND NOT EXISTS (
  SELECT 1
  FROM MESSAGE m2
  WHERE (m1.sender, m1.receiver) IN ((m2.sender, m2.receiver), (m2.receiver, m2.sender))
    AND m2.dateSent > m1.dateSent
) 
ORDER BY m1.dateSent DESC;

或者，如果您的 MySql 版本是 8.0+，请使用ROW_NUMBER()窗口函数：

SELECT sender, receiver, content, dateSent
FROM (
  SELECT *, ROW_NUMBER() OVER (
              PARTITION BY LEAST(sender, receiver), GREATEST(sender, receiver) 
              ORDER BY dateSent DESC
            ) rn
  FROM MESSAGE
  WHERE 'Gomez' IN (sender, receiver)
) m
WHERE rn = 1
ORDER BY dateSent DESC;

请参阅演示。

推荐阅读