SQLite查询：获取包含重复序列号的工作站主机名列表

直接使用以下SQL语句即可获取所有包含重复序列号的主机完整记录：

SELECT Hostname, LastSeen
FROM Assets
WHERE SUBSTR(Hostname, INSTR(Hostname, '-') + 1) IN (
    SELECT SUBSTR(Hostname, INSTR(Hostname, '-') + 1) AS serial
    FROM Assets
    GROUP BY serial
    HAVING COUNT(*) > 1
)
ORDER BY SUBSTR(Hostname, INSTR(Hostname, '-') + 1), LastSeen DESC;

逻辑说明 #

• 内层子查询复用你已实现的逻辑：提取每个主机的序列号，分组后筛选出出现次数大于1的重复序列号。
• 外层查询通过IN条件，过滤出所有序列号属于重复序列的主机记录，完整返回主机名和最后上线时间。
• 末尾的ORDER BY将同一序列号的记录归为一组，并按最后上线时间倒序排列，方便快速区分新旧设备（最新记录排在最前）。

扩展：标记待删除的旧设备 #

如果需要精准标记每个序列号下的旧设备（仅保留最新一条），可使用SQLite 3.25及以上版本支持的窗口函数：

SELECT 
    Hostname, 
    LastSeen,
    ROW_NUMBER() OVER (
        PARTITION BY SUBSTR(Hostname, INSTR(Hostname, '-') + 1) 
        ORDER BY LastSeen DESC
    ) AS rn
FROM Assets
WHERE SUBSTR(Hostname, INSTR(Hostname, '-') + 1) IN (
    SELECT SUBSTR(Hostname, INSTR(Hostname, '-') + 1) AS serial
    FROM Assets
    GROUP BY serial
    HAVING COUNT(*) > 1
)
ORDER BY SUBSTR(Hostname, INSTR(Hostname, '-') + 1), rn;

结果中rn=1的是对应序列号的最新设备，rn>1的即为需要移除的旧设备。

内容的提问来源于stack exchange，提问作者emptyh