SQL INNER JOIN返回多行时如何获取certainty列值最高的记录

两张表JOIN后出现重复记录的核心原因是dim_asset_operating_system表中，同一个asset_id + description组合存在多条certainty值不同的记录，以下是两种可落地的实现方案：

通用兼容方案（适配PostgreSQL/MySQL 8.0+/Oracle等绝大多数支持窗口函数的数据库） #

使用窗口函数按关联维度分组、按certainty倒序排序后取首位即可，可根据需求调整返回规则：

• 用ROW_NUMBER()：每个分组仅返回1条最高优先级记录，同分最高时随机取一条
• 用RANK()：同一个分组下所有certainty最高的记录全部返回

SELECT * FROM (
    SELECT
        A.ip_address,
        A.os_type,
        A.os_vendor,
        A.os_family,
        A.os_name,
        A.os_version,
        A.os_description,
        A.os_system,
        B.certainty,
        ROW_NUMBER() OVER (PARTITION BY A.asset_id, A.os_description ORDER BY B.certainty DESC) AS rn
    FROM 
        dim_asset A
    INNER JOIN 
        dim_asset_operating_system B ON A.asset_id = B.asset_id 
                                     AND A.os_description = B.description
    -- 全量查询删除该WHERE条件即可
    WHERE ip_address = '192.168.65.100'
) t WHERE rn = 1;

高性能方案（适配千万级大数据量查询场景） #

窗口函数在全量扫表时性能开销较高，用子查询提前聚合出每个分组的最高certainty值再关联过滤，配合索引可达到最优性能：

SELECT
    A.ip_address,
    A.os_type,
    A.os_vendor,
    A.os_family,
    A.os_name,
    A.os_version,
    A.os_description,
    A.os_system,
    B.certainty
FROM 
    dim_asset A
INNER JOIN 
    dim_asset_operating_system B ON A.asset_id = B.asset_id 
                                 AND A.os_description = B.description
INNER JOIN (
    -- 提前聚合每个关联组合的最高certainty值
    SELECT asset_id, description, MAX(certainty) AS max_certainty
    FROM dim_asset_operating_system
    GROUP BY asset_id, description
) B_max ON B.asset_id = B_max.asset_id 
        AND B.description = B_max.description 
        AND B.certainty = B_max.max_certainty
WHERE ip_address = '192.168.65.100';

千万级查询性能优化建议 #

• 必须新增联合索引：给dim_asset_operating_system表加联合索引 (asset_id, description, certainty)，给dim_asset表加联合索引 (asset_id, os_description)，如果经常按ip_address过滤再新增ip_address索引，可避免全表扫描，性能提升10倍以上
• 全量查询时避免用SELECT *，仅查询需要的字段减少数据传输开销
• 分页查询优先在dim_asset表完成过滤分页后再做关联，减少参与JOIN的数据量

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