SQL Server多表连接按指定维度求和求助（非Distinct方案）

我懂你现在的困扰——多表连接后出现重复行，直接求和会把重复值也算进去，导致结果不对对吧？别着急，我给你两种可行的方案，其中包括你提到的关联子查询方式，另外还有一种性能更优的先聚合再关联的方法，你可以根据自己的场景选择。

方案一：先聚合各表，再关联（推荐） #

这种方式先对每张表按AccountNo、Date、Property三个维度聚合求和，再把聚合后的结果关联起来，能避免连接时产生大量重复行，性能会更出色。假设每张表的结构对应各自的分数字段（比如Table1有Ascores，Table2有Bscores和Cscores，Table3有Dscores和Escores，你可以根据实际表结构调整）：

SELECT
    COALESCE(t1.AccountNo, t2.AccountNo, t3.AccountNo) AS AccountNo,
    COALESCE(t1.Date, t2.Date, t3.Date) AS Date,
    COALESCE(t1.Property, t2.Property, t3.Property) AS Property,
    COALESCE(t1.TotalAscores, 0) AS TotalAscores,
    COALESCE(t2.TotalBscores, 0) AS TotalBscores,
    COALESCE(t2.TotalCscores, 0) AS TotalCscores,
    COALESCE(t3.TotalDscores, 0) AS TotalDscores,
    COALESCE(t3.TotalEscores, 0) AS TotalEscores
FROM
    (SELECT AccountNo, Date, Property, SUM(Ascores) AS TotalAscores
     FROM Table1
     GROUP BY AccountNo, Date, Property) t1
FULL OUTER JOIN
    (SELECT AccountNo, Date, Property, SUM(Bscores) AS TotalBscores, SUM(Cscores) AS TotalCscores
     FROM Table2
     GROUP BY AccountNo, Date, Property) t2
ON t1.AccountNo = t2.AccountNo AND t1.Date = t2.Date AND t1.Property = t2.Property
FULL OUTER JOIN
    (SELECT AccountNo, Date, Property, SUM(Dscores) AS TotalDscores, SUM(Escores) AS TotalEscores
     FROM Table3
     GROUP BY AccountNo, Date, Property) t3
ON COALESCE(t1.AccountNo, t2.AccountNo) = t3.AccountNo 
   AND COALESCE(t1.Date, t2.Date) = t3.Date 
   AND COALESCE(t1.Property, t2.Property) = t3.Property;

这里用FULL OUTER JOIN是为了确保即使某个维度在某张表中没有数据，也能保留该维度的记录，并用COALESCE把NULL值替换为0，避免求和结果出现NULL。如果你的表之间只需要保留三个维度都存在的记录，可以把FULL OUTER JOIN换成INNER JOIN。

方案二：关联子查询实现 #

如果你更倾向于用关联子查询的方式，可以针对每个分数字段，用子查询在对应表中按维度求和：

SELECT
    base.AccountNo,
    base.Date,
    base.Property,
    COALESCE((SELECT SUM(Ascores) FROM Table1 t1 
     WHERE t1.AccountNo = base.AccountNo 
       AND t1.Date = base.Date 
       AND t1.Property = base.Property), 0) AS TotalAscores,
    COALESCE((SELECT SUM(Bscores) FROM Table2 t2 
     WHERE t2.AccountNo = base.AccountNo 
       AND t2.Date = base.Date 
       AND t2.Property = base.Property), 0) AS TotalBscores,
    COALESCE((SELECT SUM(Cscores) FROM Table2 t2 
     WHERE t2.AccountNo = base.AccountNo 
       AND t2.Date = base.Date 
       AND t2.Property = base.Property), 0) AS TotalCscores,
    COALESCE((SELECT SUM(Dscores) FROM Table3 t3 
     WHERE t3.AccountNo = base.AccountNo 
       AND t3.Date = base.Date 
       AND t3.Property = base.Property), 0) AS TotalDscores,
    COALESCE((SELECT SUM(Escores) FROM Table3 t3 
     WHERE t3.AccountNo = base.AccountNo 
       AND t3.Date = base.Date 
       AND t3.Property = base.Property), 0) AS TotalEscores
FROM
    (SELECT AccountNo, Date, Property FROM Table1
     UNION
     SELECT AccountNo, Date, Property FROM Table2
     UNION
     SELECT AccountNo, Date, Property FROM Table3) base;

这里的base子查询用UNION（自动去重）获取所有唯一的AccountNo、Date、Property组合，然后针对每个组合，用关联子查询从各表中取出对应维度的求和结果。COALESCE用来把NULL替换为0，避免出现无数据时的空值。

为什么之前的SQL不符合预期？ #

大概率是你先把三张表直接连接，再进行聚合——这会导致连接时产生的重复行被多次求和。比如如果Table1中某个维度有2条记录，Table2中同维度有3条记录，直接连接后会产生6条重复行，此时求和就会把分数计算6次，结果自然不对。所以先聚合再连接（或用关联子查询）能从根源避免这个问题。

内容的提问来源于stack exchange，提问作者Yung Lin Ma