SQL Server按ip+port组合去重取每组最大id记录的查询方法

基础信息 #

• 目标表：scan
• 表结构字段：id(自增主键)、ip(IP地址)、port(端口号)、text(文本字段)
• 存量示例数据：

id   ip           port  text
-----------------------------
1    192.168.0.1    2   text1
2    192.168.0.1    2   text2
3    192.168.0.1    2   text3
4    192.168.0.3    2   text4

查询需求规则 #

• 过滤掉ip与port组合重复的冗余记录
• 每个唯一的ip+port组合下，仅保留*id值最大（即最新插入）*的记录
• 预期输出结果：

4, 192.168.0.3,2, text4
3, 192.168.0.1,2, text3

可直接运行的T-SQL实现语句 #

方案1：窗口函数实现（推荐） #

适用于SQL Server 2005及以上版本，代码简洁，大表查询性能更优：

WITH scan_ranked AS (
    SELECT
        id,
        ip,
        port,
        [text],
        -- 按ip+port拆分独立分组，组内按id倒序排列，最新记录排首位
        ROW_NUMBER() OVER (PARTITION BY ip, port ORDER BY id DESC) AS row_rank
    FROM scan
)
SELECT id, ip, port, [text]
FROM scan_ranked
WHERE row_rank = 1
ORDER BY id DESC;

方案2：聚合关联实现 #

适用于所有版本的SQL Server，逻辑直观易理解：

SELECT s.id, s.ip, s.port, s.[text]
FROM scan s
INNER JOIN (
    -- 先计算每个ip+port组合下的最大id
    SELECT ip, port, MAX(id) AS latest_id
    FROM scan
    GROUP BY ip, port
) latest ON s.id = latest.latest_id
ORDER BY s.id DESC;

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