时间戳

首页 > 解决方案 > 具有最新时间戳的 SQL 记录，但带有枚举用户的连接，其中不是特定状态

mysql - 具有最新时间戳的 SQL 记录，但带有枚举用户的连接，其中不是特定状态

问题描述

真的很难在这个上匹配其他人的例子，所以想知道是否有人会足够好来为我指出正确的方向......

我拥有的是 MySQL 中的 2 个表。

标签

tagid, status, lot, lat, long, createuser, timestamp

用户

userid, first, surname

我的过程只是将行添加到标签表中，对于扫描的 tagid，因此可能有许多行具有相同的 tagid，但根据用户的不同，每一行都会有不同的信息，每一行都有发生时间的时间戳。

问题是我想列出每个 tagid 的最新记录，但我想排除带有“store”标签的任何内容，并将 Tags.createuser 枚举为 Users.userid 的名称

鉴于可能存在如下情况，我只是无法弄清楚如何获取最后一个时间戳以及 NOT 语句。

tagid, status, lot, lat, long, createuser, timestamp
1000001, live, 1, xxxx, yyyy, 1, 2020-10-20 12:00
1000001, store, 1, xxxx, yyyy, 1, 2020-10-20 12:10
1000002, live, 1, xxxx, yyyy, 2, 2020-10-20 11:00

用户 2 = 乔博客

所以我唯一想要返回的是下面，因为 1000001 的最后一条记录是“商店”

1000002, live, 1, xxxx, yyyy, Joe Bloggs, 2020-10-20 11:00

标签： mysqlsqldatetimeinner-joingreatest-n-per-group

解决方案

您需要每个标签的最新记录以及相关的用户名 - 当且仅当该标签的状态为“实时”时。

您可以使用row_number()和过滤：

select t.*, u.surname
from users u
inner join (
    select t.*, row_number() over(partition by tagid order by timestamp desc) rn
    from tags
) t on t.createduser = u.userid
where t.rn = 1 and t.status = 'live'

这需要 MySQL 8.0。在早期版本中，一个选项使用相关子查询进行过滤：

select t.*, u.surname
from users u
inner join tags t on t.createduser = u.userid
where t.status = 'live' and t.timestamp = (
    select max(t1.timestamp) from tags t1 where t1.tagid = t.tagid
)

推荐阅读