如何编写SQL查询优先保留ONEIL源系统的重复facility_id数据

需求说明 #

现有一张存储设施信息的表，包含字段complete_building_id、facility_id、LTP_facility_id、source_system。其中同一facility_id可能对应多个source_system（即重复行），不同来源的complete_building_id不同。要求查询全表时自动去重：

• 若某facility_id存在多个来源，仅保留source_system为ONEIL的行
• 若某facility_id仅存在单一来源（如仅SECUREBASE），直接保留该行

示例数据：

对于facility_id=002216，仅需保留ONEIL来源的行；对于仅存在单一来源的facility_id=003314，直接保留对应行。

解决方案 #

方法1：使用窗口函数（通用型，支持MySQL 8+、PostgreSQL、SQL Server等） #

这是最简洁高效的写法，利用ROW_NUMBER()窗口函数为每个facility_id分组内的行排序，优先保留ONEIL来源：

SELECT complete_building_id, facility_id, LTP_facility_id, source_system
FROM (
    SELECT 
        *,
        -- 按facility_id分组，ONEIL来源排第一
        ROW_NUMBER() OVER (
            PARTITION BY facility_id 
            ORDER BY CASE WHEN source_system = 'ONEIL' THEN 0 ELSE 1 END
        ) AS row_rank
    FROM your_table_name -- 替换为你的表名
) ranked_data
WHERE row_rank = 1;

逻辑说明：

• PARTITION BY facility_id：将相同facility_id的行划分为同一组
• ORDER BY CASE：给ONEIL来源的行分配排序值0，其他来源分配1，确保ONEIL行在分组内排第一
• WHERE row_rank = 1：仅保留每个分组内排序第一的行

方法2：使用JOIN与条件筛选（兼容老版本数据库，如MySQL 5.x） #

如果你的数据库不支持窗口函数，可以用关联查询实现：

SELECT t1.*
FROM your_table_name t1 -- 替换为你的表名
LEFT JOIN your_table_name t2
    ON t1.facility_id = t2.facility_id
    AND t2.source_system = 'ONEIL'
WHERE 
    -- 若存在ONEIL来源，仅保留该行
    t1.source_system = 'ONEIL'
    -- 若不存在ONEIL来源，保留当前行（即该facility_id无重复）
    OR t2.facility_id IS NULL;

逻辑说明：

• 通过左连接匹配同facility_id下的ONEIL行
• 条件1：直接筛选出所有ONEIL来源的行
• 条件2：当左连接无匹配（即该facility_id没有ONEIL来源），保留原表中的行

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