如何删除包含 LEFT OUTER JOIN 的 T-SQL 查询中的重复条目

Question

我在从包含多个 LEFT OUTER JOIN 的查询中取出重复条目时遇到问题

SELECT s.Category,
       SUM(spons.SPONS) AS SPONSOREDAMT,
       SUM(priv.PRIV)   AS PRIVATEAMT FROM   (SELECT ID   AS ID,
               Name AS Category FROM   dbo.ServicesTable) s
       LEFT OUTER JOIN (SELECT DISTINCT ( ServiceId )   AS ServiceId,
                                        DateCreated,
                                        SUM(SPONSORAMT) AS SPONS
                        FROM   dbo.SponsorMonthlyBill
                        GROUP  BY ServiceId,
                                  DateCreated) spons
         ON ( s.ID = spons.ServiceId )
       LEFT OUTER JOIN (SELECT DISTINCT ( ServiceId )   AS ServiceId,
                                        DateCreated,
                                        SUM(PRIVATEAMT) AS PRIV
                        FROM   dbo.PrivateMonthlyBill
                        GROUP  BY ServiceId,
                                  DateCreated) priv
         ON ( s.ID = priv.ServiceId )
            AND ( MONTH(priv.DateCreated) = MONTH('2011-08-04') )
GROUP  BY s.Category,
          spons.SPONS,
          priv.PRIV 

如果我使最后一行看起来像这样：

GROUP BY s.Category 

而不是现在的样子，我将添加重复条目。如果有多个重复条目，则将所有 3 个或 4 个或更多条目相加。那么这件事该怎么办呢。

Scorpi0 给出了一个有效的答案，但需要一些额外的修改。这是我对其所做的修改：

SELECT  s.Name AS Category,
   SUM(spons.SPONS) AS SPONSOREDAMT,
   SUM(priv.PRIV) AS PRIVATEAMT, SUM(spons.SPONS) + SUM(priv.PRIV) as 
   TOTAL,spons.DateCreated

   FROM   dbo.ServicesTable s
   LEFT OUTER JOIN (SELECT ServiceId,DateCreated,
                           SUM(SPONSORAMT) AS SPONS
                    FROM   dbo.SponsorMonthlyBill
                    GROUP  BY ServiceId,DateCreated) spons
     ON ( s.ID = spons.ServiceId )
   LEFT OUTER JOIN (SELECT distinct ServiceId,
                           SUM(PRIVATEAMT) AS PRIV
                    FROM   dbo.PrivateMonthlyBill
                    GROUP  BY ServiceId
                    ) priv
     ON ( s.ID = priv.ServiceId )
GROUP  BY s.Name,spons.DateCreated

我知道它再次引入了重复项，但是如何将 DateCreated 保留在 GROUP BY 之外，并仍然在主 SELECT 子句中访问它？请记住，这是一个视图，并且它坚持必须遵循一些规则。

Answer 1

您的重复项来自 DateCreated 的无用组。

尝试使用此查询，它应该会给您正确的结果：

SELECT s.Name AS Category,
       SUM(spons.SPONS) AS SPONSOREDAMT,
       SUM(priv.PRIV)   AS PRIVATEAMT
FROM   dbo.ServicesTable s
       LEFT OUTER JOIN (SELECT ServiceId,
                               SUM(SPONSORAMT) AS SPONS
                        FROM   dbo.SponsorMonthlyBill
                        GROUP  BY ServiceId) spons
         ON ( s.ID = spons.ServiceId )
       LEFT OUTER JOIN (SELECT ServiceId
                               SUM(PRIVATEAMT) AS PRIV
                        FROM   dbo.PrivateMonthlyBill
                        WHERE MONTH(DateCreated) = MONTH('2011-08-04')
                        GROUP  BY ServiceId
                        ) priv
         ON ( s.ID = priv.ServiceId )
GROUP  BY s.Name