mysql - 对于特定的 user_id 获取该用户与所有其他用户在 MySQL 中的最新行(按时间)

Question

我对 MySQL 不是很熟悉。

我有一个表，其中包含:unique_id(auto_increment)、user_id、other_user_id、timestamp 和一些其他字段。

假设我有一些值:

unique_id user_id other_user_id     timestamp
  1          10       11          yyyy-mm-dd hh:mm:ss
  2          11       10          yyyy-mm-dd hh:mm:ss
  3          11       12          yyyy-mm-dd hh:mm:ss
  4          11       10          yyyy-mm-dd hh:mm:ss
  5          10       12          yyyy-mm-dd hh:mm:ss

我想做的是:对于单个用户(假设上面的 user_id = 10)，我想找到他与所有其他用户(例如 11、12)所在的所有行，并只获取最后一个(按时间)。

因此对于上面的数据，结果应该是第 4 行和第 5 行。第 4 行是因为它是在 10 到 11 之间找到的最后一个。第 5 行是因为它是在 10 之间找到的最后一个(在本例中也是唯一一个)和 12。对于任何其他行，第 10 行与其他用户相同。

查询的内容是什么？

Answer 1

您可以使用如下查询:

SELECT t1.*
FROM mytable
JOIN (
   SELECT LEAST(user_id, other_user_id) AS user1,
          GREATEST(user_id, other_user_id) AS user2,
          MAX(unique_id) AS max_id
   FROM mytable
   GROUP BY LEAST(user_id, other_user_id),
            GREATEST(user_id, other_user_id)
) AS t2 ON t1.unique_id = t2.max_id

派生表用于提取与每个 user_id, other_user_id 对的最新记录关联的 unique_id 值。由于这大概也是表的 PK，我们可以使用该值来获取相关记录。