我请求您帮助我筛选一个有3列的表(扫描的标签ID、扫描的日期/时间、扫描的mac地址(。
tagID | tme | mac |
---|---|---|
000A0000B000C000D000E000F | 04.07.2021T11:44:08Z | 63584030653439 |
000A0000B000C000D000E000F | 04.07.2021T12.59.01Z | 73594035653865 |
000A0000B000C000D000E000F | 2021T年7月4日11:44:08Z | 63584030653439 |
120A0000B000C000D000E000F | 04.07.2021T12.59.01Z | 73594035653865 |
130A0000B000C000D000E000F | 04.07.2021T11:44:08Z | 63584030653439 |
140A0000B000C000D000E000F | 2021T12.59.01Z | 73594035653865 |
150A0000B000C000D000E000F | 2021T04.011:44:08Z | 63584030653439 |
550A0000B000C000D000E000F | 04.07.2021T12.59.01Z | 73594035653865 |
0067895B0000C000D000E000F | 04.07.2021T11:44:08Z | 63584030653439 |
0007695B000C000D000E000F | 04.07.2021T12.59.01Z | 73594035653865 |
00459ASB0000C000D000E000F | 04.07.2021T11:44:08Z | 63584030653439|
0235466B000D000E000F | 04.07.2021T12.59.01Z | 73594035653865 |
在标准SQL中,这看起来像这样:
select t.*
from (select t.*,
row_number() over (partition by mac order by tme desc) as seqnum
from t
where tagID = '000A000B000C000D000E000F' and
tme >= current_date and
tme < current_date + interval '1 day'
) t
where seqnum = 1;
日期/时间函数因数据库而异,因此您可能需要为数据库调整这些函数。
注意:如果只有这三列,那么聚合就足够了:
select mac, tagID, max(tme)
from t
where tagID = '000A000B000C000D000E000F' and
tme >= current_date and
tme < current_date + interval '1 day'
group by mac, tagID