SQL基于指定列去重：合并多源查询结果时忽略日期列

嘿，我完全懂你现在的困扰——原本想合并两个数据源的软件安装记录，结果因为日期列的差异（有的有值有的空，或者值不一样），导致明明是同一台机器同一个软件的记录，却被当成不同结果保留下来了对吧？从你贴的示例图也能看到，同一台CH开头机器上的同一个软件，因为InstallDate和InstallDate2的不同出现了重复，咱们调整下查询逻辑就能搞定！

核心思路是：先把两个数据源的所有记录合并，然后按你想要唯一的四个字段（PCName、Vendor、Product、Version）分组，每组只保留一条记录（日期字段可以选择取非空值，或者优先保留某一个数据源的日期）。下面给你两种常用的解决方案：

方案一：分组聚合（简单高效，适合不在乎日期取哪一个的场景） #

先用UNION ALL合并两个数据源的所有记录（比UNION更高效，因为不需要提前去重），然后按四个关键字段分组，日期字段用聚合函数取非空值：

WITH CombinedData AS (
    -- 第一个数据源的查询
    SELECT 
        SYS.Netbios_Name0 as PCName, 
        ARP.Publisher0 as Vendor, 
        ARP.DisplayName0 as Product, 
        ARP.Version0 as Version, 
        REPLACE(REPLACE(CONVERT(varchar,ARP.InstallDate0,120),'-',''),' 00:00:00','') as InstallDate,
        REPLACE(REPLACE(CONVERT(varchar,ARP.InstallDate0,120),'-',''),' 00:00:00','') as InstallDate2
    FROM v_Add_Remove_Programs ARP 
    JOIN v_R_System SYS ON ARP.ResourceID=SYS.ResourceID 
    WHERE SYS.Netbios_Name0 like 'CH-%' and InstallDate0 NOT LIKE ''
    
    UNION ALL -- 用UNION ALL避免不必要的去重，提升性能
    
    -- 第二个数据源的查询
    SELECT 
        SYS.Netbios_Name0 as PCName, 
        SP.CompanyName as Vendor, 
        SP.ProductName as Product, 
        SP.ProductVersion as Version, 
        REPLACE(REPLACE(CONVERT(varchar,MARP.InstallDate0,120),'-',''),' 00:00:00','') as InstallDate,
        REPLACE(REPLACE(CONVERT(varchar,GSI.InstallDate0,120),'-',''),' 00:00:00','') as InstallDate2
    FROM v_GS_SoftwareProduct SP 
    JOIN v_R_System SYS ON SP.ResourceID=SYS.ResourceID 
    LEFT JOIN v_GS_Mapped_Add_Remove_Programs MARP ON SP.ResourceID = MARP.ResourceID 
        AND RTRIM(LTRIM(UPPER(SP.ProductName))) LIKE RTRIM(LTRIM(UPPER(MARP.DisplayName0))) 
        AND RTRIM(LTRIM(UPPER(SP.ProductVersion))) LIKE RTRIM(LTRIM(UPPER(MARP.Version0))) 
    LEFT JOIN v_GS_INSTALLED_SOFTWARE GSI ON SP.ResourceID = GSI.ResourceID 
        AND RTRIM(LTRIM(UPPER(SP.ProductName))) LIKE RTRIM(LTRIM(UPPER(GSI.ProductName0))) 
        AND RTRIM(LTRIM(UPPER(SP.ProductVersion))) LIKE RTRIM(LTRIM(UPPER(GSI.ProductVersion0))) 
    Where SYS.Netbios_Name0 Like 'CH-%' AND (MARP.InstallDate0 NOT LIKE '' OR GSI.InstallDate0 NOT LIKE '')
)
-- 分组去重，日期取非空值
SELECT 
    PCName,
    Vendor,
    Product,
    Version,
    COALESCE(MAX(InstallDate), '') as InstallDate, -- 取该组中第一个非空的InstallDate
    COALESCE(MAX(InstallDate2), '') as InstallDate2
FROM CombinedData
GROUP BY PCName, Vendor, Product, Version -- 按四个关键字段分组
ORDER By PCName, Vendor, Product, Version

方案二：窗口函数（适合优先保留某一个数据源记录的场景） #

如果想优先保留第一个数据源（v_Add_Remove_Programs）的记录，只有当第一个数据源没有的时候才取第二个的，可以给每个数据源标记优先级，用窗口函数排序后取每组第一条：

WITH CombinedData AS (
    -- 第一个数据源，标记优先级为1（更高优先级）
    SELECT 
        SYS.Netbios_Name0 as PCName, 
        ARP.Publisher0 as Vendor, 
        ARP.DisplayName0 as Product, 
        ARP.Version0 as Version, 
        REPLACE(REPLACE(CONVERT(varchar,ARP.InstallDate0,120),'-',''),' 00:00:00','') as InstallDate,
        REPLACE(REPLACE(CONVERT(varchar,ARP.InstallDate0,120),'-',''),' 00:00:00','') as InstallDate2,
        1 as SourcePriority
    FROM v_Add_Remove_Programs ARP 
    JOIN v_R_System SYS ON ARP.ResourceID=SYS.ResourceID 
    WHERE SYS.Netbios_Name0 like 'CH-%' and InstallDate0 NOT LIKE ''
    
    UNION ALL
    
    -- 第二个数据源，标记优先级为2（更低优先级）
    SELECT 
        SYS.Netbios_Name0 as PCName, 
        SP.CompanyName as Vendor, 
        SP.ProductName as Product, 
        SP.ProductVersion as Version, 
        REPLACE(REPLACE(CONVERT(varchar,MARP.InstallDate0,120),'-',''),' 00:00:00','') as InstallDate,
        REPLACE(REPLACE(CONVERT(varchar,GSI.InstallDate0,120),'-',''),' 00:00:00','') as InstallDate2,
        2 as SourcePriority
    FROM v_GS_SoftwareProduct SP 
    JOIN v_R_System SYS ON SP.ResourceID=SYS.ResourceID 
    LEFT JOIN v_GS_Mapped_Add_Remove_Programs MARP ON SP.ResourceID = MARP.ResourceID 
        AND RTRIM(LTRIM(UPPER(SP.ProductName))) LIKE RTRIM(LTRIM(UPPER(MARP.DisplayName0))) 
        AND RTRIM(LTRIM(UPPER(SP.ProductVersion))) LIKE RTRIM(LTRIM(UPPER(MARP.Version0))) 
    LEFT JOIN v_GS_INSTALLED_SOFTWARE GSI ON SP.ResourceID = GSI.ResourceID 
        AND RTRIM(LTRIM(UPPER(SP.ProductName))) LIKE RTRIM(LTRIM(UPPER(GSI.ProductName0))) 
        AND RTRIM(LTRIM(UPPER(SP.ProductVersion))) LIKE RTRIM(LTRIM(UPPER(GSI.ProductVersion0))) 
    Where SYS.Netbios_Name0 Like 'CH-%' AND (MARP.InstallDate0 NOT LIKE '' OR GSI.InstallDate0 NOT LIKE '')
),
RankedData AS (
    -- 给每组（PCName、Vendor、Product、Version）的记录按优先级排序
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY PCName, Vendor, Product, Version ORDER BY SourcePriority) as RowNum
    FROM CombinedData
)
-- 只取每组的第一条记录
SELECT 
    PCName,
    Vendor,
    Product,
    Version,
    InstallDate,
    InstallDate2
FROM RankedData
WHERE RowNum = 1
ORDER By PCName, Vendor, Product, Version

小提示 #

• 如果日期列你完全不在乎具体值，方案一的分组聚合更简单直接；
• 如果想优先保留某一个数据源的日期信息，方案二的窗口函数更灵活；
• 用UNION ALL代替UNION是因为UNION会自动去重，但这里因为日期不同去不了，反而会增加不必要的性能开销，所以用UNION ALL更高效。

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