SQL多表关联查询因AccountTypeCode多值导致行重复的解决及优化问询

你遇到的重复本质是BIF003表中单个C_ACCOUNT存在多条不同C_ACCOUNTTYPE、C_DIVISION的记录，左连接时会和主表BIF030的单条记录产生1:N的匹配，最终生成多行重复数据，SELECT DISTINCT只能去完全重复的行，无法解决字段不同的半重复问题，可根据你的业务需求选择对应方案：

方案1：不需要展示账户类型、分区字段 #

直接删除查询中ACCOUNTTYPECODE、ACCOUNTTYPE、ZONE_DIVISONCODE、ZONE_DIVISION四个字段，同时删除对应的BIF003、CON013、CON028三张关联表即可，无需使用DISTINCT就能得到无重复的结果。

方案2：需要保留账户类型、分区字段 #

先对BIF003表按账户去重，再关联主表即可，常用去重方式为窗口函数取指定规则的单条记录（比如取最新生效的账户类型），改写后的完整SQL如下：

SELECT 
    BIF030.C_ACCOUNT AS ACCOUNTNUMBER,
    BIF003_SINGLE.C_ACCOUNTTYPE AS ACCOUNTTYPECODE,
    CON013.C_DESCRIPTION AS ACCOUNTTYPE,
    BIF003_SINGLE.C_DIVISION AS ZONE_DIVISONCODE,
    CON028.C_DESCRIPTION AS ZONE_DIVISION,
    BIF030.C_METER as METERNUMBER,
    BIF005.C_METERCUSTOM1 AS REGISTERNUMBER,
    CONVERT(DECIMAL(20,2), BIF030.N_CONSUMP) AS CONSUMPTION,       
    CON007.C_DESCRIPTION AS UNITS,       
    BIF030.T_READDATE AS READINGDATE,
    MONTH(BIF030.T_READDATE) AS READINGMONTH,
    DAY(BIF030.T_READDATE) AS READINGDAY,
    YEAR(BIF030.T_READDATE) AS READINGYEAR,
    BIF030.I_DAYS AS READINGDAYSCOUNT
FROM ADVANCED.BIF030     
LEFT JOIN ADVANCED.CON007 ON CON007.C_UNITS=BIF030.C_UNITS
LEFT JOIN ADVANCED.BIF005 ON BIF005.C_METER=BIF030.C_METER
-- 替换成去重后的BIF003子查询
LEFT JOIN (
    SELECT * FROM (
        SELECT 
            C_ACCOUNT,
            C_ACCOUNTTYPE,
            C_DIVISION,
            -- 可替换ORDER BY后的字段为业务规则的优先级字段，比如生效时间倒序取最新
            ROW_NUMBER() OVER(PARTITION BY C_ACCOUNT ORDER BY C_ACCOUNTTYPE ASC) AS rn
        FROM ADVANCED.BIF003
    ) t WHERE rn =1
) BIF003_SINGLE ON BIF003_SINGLE.C_ACCOUNT=BIF030.C_ACCOUNT
LEFT JOIN ADVANCED.CON013 ON CON013.C_ACCOUNTTYPE=BIF003_SINGLE.C_ACCOUNTTYPE
LEFT JOIN ADVANCED.CON028 ON CON028.C_DIVISION=BIF003_SINGLE.C_DIVISION
WHERE T_READDATE > '2014-01-01'
ORDER BY ACCOUNTNUMBER, READINGDATE ASC

如果需要展示同一个账户下的所有账户类型，可使用聚合函数合并字段，比如SQL Server用STRING_AGG(CON013.C_DESCRIPTION, ',')，MySQL用GROUP_CONCAT(CON013.C_DESCRIPTION SEPARATOR ',')，其余非聚合字段加在GROUP BY子句中即可。

• 移除冗余DISTINCT：解决关联导致的1:N匹配问题后，即可删掉DISTINCT关键字，避免全字段排序去重的性能损耗。
• 新增关联与过滤索引：给BIF030的T_READDATE（WHERE过滤字段）、C_ACCOUNT、C_METER、C_UNITS（关联字段）加索引，给BIF003的C_ACCOUNT（关联分组字段）加索引，其余关联表的关联键也可按需加索引，大幅提升查询速度。
• 优化日期格式：WHERE条件中的日期使用数据库标准格式，避免隐式类型转换导致索引失效，比如统一用'2014-01-01'格式。
• 移除不必要的关联：如果后续查询不需要部分关联表的字段，直接删掉对应关联逻辑，减少计算量。

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