SQL Server：筛选存在非指定列差异的TrackID全量行

我有一个包含31列的大型表[MasterTable]，每个[TrackID]对应5行数据。除关联其他表的[ResultFK]和[ParamFK]列外，其余列本应完全一致。我的最终目标是将[ResultFK]和[ParamFK]提取到另一关联表，使[TrackID]成为主键，但发现部分记录中本应一致的列存在差异。现需在SQL Server中筛选出所有存在非ResultFK、ParamFK列差异的TrackID的全部行，以便同事确认正确值。

示例数据（未列出全部31列） #

期望结果 #

提取存在差异的TrackID的全部记录（如FR_Soil），供同事确认正确值（比如SubTypeFK需统一为13或14）。无需列出全部31列，但查询需包含所有列以识别差异：

解决方案 #

方法1：使用窗口函数识别差异TrackID #

通过计算每个TrackID下各非FK列的不同值数量，筛选出存在差异的TrackID，再关联原表获取全部行：

WITH TrackDifferences AS (
    SELECT 
        TrackID,
        COUNT(DISTINCT CountryFK) OVER (PARTITION BY TrackID) AS CountryFK_Distinct,
        COUNT(DISTINCT CRLFK) OVER (PARTITION BY TrackID) AS CRLFK_Distinct,
        COUNT(DISTINCT CorineLUFK) OVER (PARTITION BY TrackID) AS CorineLUFK_Distinct,
        COUNT(DISTINCT SubTypeFK) OVER (PARTITION BY TrackID) AS SubTypeFK_Distinct,
        COUNT(DISTINCT ProvidedFK) OVER (PARTITION BY TrackID) AS ProvidedFK_Distinct,
        COUNT(DISTINCT GridTypeFK) OVER (PARTITION BY TrackID) AS GridTypeFK_Distinct
        -- 按此格式继续添加其余25个非ResultFK/ParamFK的列
    FROM MasterTable
)
SELECT mt.*
FROM MasterTable mt
JOIN (
    SELECT DISTINCT TrackID
    FROM TrackDifferences
    -- 只要任意一个非FK列的不同值数量大于1，说明存在差异
    WHERE CountryFK_Distinct > 1 
       OR CRLFK_Distinct > 1 
       OR CorineLUFK_Distinct > 1 
       OR SubTypeFK_Distinct > 1 
       OR ProvidedFK_Distinct > 1 
       OR GridTypeFK_Distinct > 1
       -- 继续添加其余列的判断条件
) td ON mt.TrackID = td.TrackID;

方法2：使用GROUP BY和HAVING筛选差异TrackID #

通过分组（按TrackID+所有非FK列），如果分组数大于1，说明该TrackID存在差异：

WITH TrackGroupCounts AS (
    SELECT TrackID, COUNT(*) AS GroupCount
    FROM MasterTable
    GROUP BY TrackID, CountryFK, CRLFK, CorineLUFK, SubTypeFK, ProvidedFK, GridTypeFK
             -- 按此格式继续添加其余25个非ResultFK/ParamFK的列
)
SELECT mt.*
FROM MasterTable mt
JOIN TrackGroupCounts tgc ON mt.TrackID = tgc.TrackID
WHERE tgc.GroupCount > 1;

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