SQL自连接查询：排除同账户下多表计相同日期、编码的重复记录

需求描述 #

每个账户（Account）下可关联多个计量表（Meter），每个Meter都对应STARTDATE、ENDDATE、Code三个字段，需要过滤掉同账户下不同Meter的这三个字段完全相同的记录，仅保留这三个字段不存在跨Meter重复的记录。

样例表数据 #

Account Meter   STARTDATE   ENDDATE     Code            
1       101     4/10/2019   4/9/2020    ***Custom***    
1       109     4/10/2019   4/9/2020    ***Custom***            
1       101     4/22/2020   12/31/9999  HOLDOVER-ETF0_APR20 
1       109     4/22/2020   12/31/9999  HOLDOVER-ETF0_APR20         
2       202     1/25/2020   4/23/2020   GE_PMD_JAN20    
2       208     2/24/2020   4/23/2020   GE_PMD_JAN20            
2       202     4/24/2020   12/31/9999  GE-V-STD-ETF0
2       208     4/24/2020   12/31/9999  GE-V-STD-ETF0

期望查询结果 #

2       202     1/25/2020   4/23/2020   GE_PMD_JAN20    
2       208     2/24/2020   4/23/2020   GE_PMD_JAN20            

原查询问题说明 #

你编写的SQL逻辑存在偏差：where条件要求三个字段全部都不相等，完全背离了「过滤不同Meter三字段完全相同的记录」的需求，同时自连接的写法会产生多余的笛卡尔积数据，最终返回结果不符合预期。

正确实现方案 #

方案1：窗口函数实现（兼容SQL Server、PostgreSQL、MySQL 8.0+等主流数据库） #

核心逻辑：按Account、STARTDATE、ENDDATE、Code分组，统计每个分组内的不同Meter数量，仅保留分组内只有1个Meter的记录，就是目标结果。

SELECT account, meter, STARTDATE, ENDDATE, Code
FROM (
    SELECT *,
        COUNT(DISTINCT Meter) OVER (
            PARTITION BY Account, STARTDATE, ENDDATE, Code
        ) AS same_group_meter_cnt
    FROM #MultipleMeter
) t
WHERE same_group_meter_cnt = 1
ORDER BY account, STARTDATE

方案2：子查询分组实现（兼容低版本不支持窗口函数的数据库） #

SELECT m.*
FROM #MultipleMeter m
INNER JOIN (
    SELECT Account, STARTDATE, ENDDATE, Code
    FROM #MultipleMeter
    GROUP BY Account, STARTDATE, ENDDATE, Code
    HAVING COUNT(DISTINCT Meter) = 1
) valid_group
ON m.Account = valid_group.Account
AND m.STARTDATE = valid_group.STARTDATE
AND m.ENDDATE = valid_group.ENDDATE
AND m.Code = valid_group.Code
ORDER BY m.account, m.STARTDATE

两种方案都可以输出你期望的结果。

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