如何在SQL中筛选并保留相同FQDN/NAME的最新TIMESTAMP记录？

我有一张表，其中存在FQDN和NAME相同但TIMESTAMP不同的重复记录，需要移除旧记录，只保留每组相同FQDN和NAME对应的最新记录。

目前我使用的SQL查询语句如下：

SELECT devices.fqdn, ports.pid, ports.name, ports.`status`, `subStatus`.`substatus`, updateLog.previousValue, updateLog.newValue, `updateLog`.`user`, `updateLog`.`timestamp`, `user`.`email`
FROM `updateLog`
LEFT JOIN `user` ON user.id = updateLog.user 
LEFT JOIN ports ON updateLog.rowId=ports.pid
LEFT JOIN subStatus ON ports.subStatus=subStatus.id
LEFT JOIN slots ON ports.slot=slots.sid
LEFT JOIN devices ON slots.device=devices.did 
WHERE (updateLog.rowId IN (select pid from ports where status=2)) 
AND (updateLog.columnName = 'status' AND updateLog.tableName = 'ports' and updateLog.newValue=2) 
ORDER BY ports.pid asc LIMIT 1000;

可行实现方案 #

方案1：使用窗口函数（支持MySQL 8.0+、PostgreSQL、SQL Server等） #

通过ROW_NUMBER()窗口函数给每组(FQDN, NAME)按时间戳倒序排名，只取排名第1的记录，就是每组的最新数据：

WITH ranked_logs AS (
    SELECT 
        devices.fqdn, 
        ports.pid, 
        ports.name, 
        ports.`status`, 
        `subStatus`.`substatus`, 
        updateLog.previousValue, 
        updateLog.newValue, 
        `updateLog`.`user`, 
        `updateLog`.`timestamp`, 
        `user`.`email`,
        -- 按FQDN和NAME分组，时间戳倒序排序，给每条记录标记排名
        ROW_NUMBER() OVER (PARTITION BY devices.fqdn, ports.name ORDER BY updateLog.timestamp DESC) AS rn
    FROM `updateLog`
    LEFT JOIN `user` ON user.id = updateLog.user 
    LEFT JOIN ports ON updateLog.rowId=ports.pid
    LEFT JOIN subStatus ON ports.subStatus=subStatus.id
    LEFT JOIN slots ON ports.slot=slots.sid
    LEFT JOIN devices ON slots.device=devices.did 
    WHERE (updateLog.rowId IN (select pid from ports where status=2)) 
      AND (updateLog.columnName = 'status' AND updateLog.tableName = 'ports' and updateLog.newValue=2)
)
-- 筛选出每组排名为1的最新记录
SELECT fqdn, pid, name, `status`, substatus, previousValue, newValue, `user`, `timestamp`, email
FROM ranked_logs
WHERE rn = 1
ORDER BY pid asc LIMIT 1000;

方案2：子查询关联（适配低版本MySQL等不支持窗口函数的数据库） #

如果你的数据库版本不支持窗口函数，可以先通过子查询找出每组(FQDN, NAME)的最大时间戳，再关联原查询结果筛选出对应记录：

SELECT 
    d.fqdn, 
    p.pid, 
    p.name, 
    p.`status`, 
    ss.substatus, 
    ul.previousValue, 
    ul.newValue, 
    ul.`user`, 
    ul.`timestamp`, 
    u.email
FROM `updateLog` ul
LEFT JOIN `user` u ON u.id = ul.user 
LEFT JOIN ports p ON ul.rowId=p.pid
LEFT JOIN subStatus ss ON p.subStatus=ss.id
LEFT JOIN slots s ON p.slot=s.sid
LEFT JOIN devices d ON s.device=d.did 
-- 关联子查询得到的每组最新时间戳
JOIN (
    SELECT 
        devices.fqdn, 
        ports.name, 
        MAX(updateLog.timestamp) AS max_ts
    FROM `updateLog`
    LEFT JOIN ports ON updateLog.rowId=ports.pid
    LEFT JOIN slots ON ports.slot=slots.sid
    LEFT JOIN devices ON slots.device=devices.did 
    WHERE (updateLog.rowId IN (select pid from ports where status=2)) 
      AND (updateLog.columnName = 'status' AND updateLog.tableName = 'ports' and updateLog.newValue=2)
    GROUP BY devices.fqdn, ports.name
) AS latest ON d.fqdn = latest.fqdn AND p.name = latest.name AND ul.timestamp = latest.max_ts
WHERE (ul.rowId IN (select pid from ports where status=2)) 
  AND (ul.columnName = 'status' AND ul.tableName = 'ports' and ul.newValue=2)
ORDER BY p.pid asc LIMIT 1000;

注意：如果同一组(FQDN, NAME)下有多个记录时间戳完全相同，方案1会随机保留一条，方案2会保留所有时间戳等于最大值的记录，可根据实际需求调整。

内容的提问来源于stack exchange，提问作者Антон Цуркану