按 + 分组加入

Question

您好，我在使用 Group By 和 3 个表之间的连接时遇到问题。

我有一个包含各种字段和项目代码字段的项目表。然后，我有一个发票表和一个工时表，每个表每个项目可以有多行。这两个表也有项目代码。

这两个 SUM 值计算不正确，我真的很难找出问题所在。

这是我正在使用的 sql：

SELECT  dbo.project.projectcode, 
        dbo.project.client, 
        dbo.project.project, 
        dbo.project.budget, 
        dbo.project.budget * 80 AS value, 
        SUM(dbo.harvest.hours) AS hourslogged, 
        SUM(dbo.salesforce.value) AS invoiced
FROM  dbo.salesforce 
    RIGHT OUTER JOIN dbo.project 
        ON dbo.salesforce.projectcode = dbo.project.projectcode 
    LEFT OUTER JOIN dbo.harvest 
        ON dbo.project.projectcode = dbo.harvest.projectcode
GROUP BY    dbo.project.projectcode, 
            dbo.salesforce.projectcode, 
            dbo.harvest.projectcode, 
            dbo.project.project, 
            dbo.project.client, 
            dbo.project.budget

任何有关此的帮助或提示将不胜感激！

Answer 1

每当 dbo.salesforce 和 dbo.harvest 这两个表中的每个 projectcode 都有超过 1 个匹配项时，就会生成一个迷你笛卡尔积发生。这是一个简单的例子。假设有表 A 和 B，如下所示：

• Table A:

  援助价值
  --- --------
  1 值A1
  2 值A2
  

• 表B：

  BID BVALUE AID
  --- ------- ---
  1 值B1 1
  2 值B2 1
  3 值B3 2
  

现在如果我们执行此连接：

SELECT * FROM A JOIN B ON A.AID = B.AID

结果将是：

AID  AVALUE   BID  BVALUE   AID
---  -------  ---  -------  ---
1    ValueA1  1    ValueB1  1
1    ValueA1  2    ValueB2  1
2    ValueA2  3    ValueB3  2

输入表C：

CID  CVALUE   AID
---  -------  ---
1    ValueC1  1
2    ValueC2  1
3    ValueC3  1

现在的连接是这样的

SELECT * FROM A JOIN B ON A.AID = B.AID JOIN C ON A.AID = C.AID

：结果是什么？此处：

AID  AVALUE   BID  BVALUE   AID  CID  CVALUE   AID
---  -------  ---  -------  ---  ---  -------  ---
1    ValueA1  1    ValueB1  1    1    ValueC1  1
1    ValueA1  1    ValueB1  1    2    ValueC2  1
1    ValueA1  1    ValueB1  1    3    ValueC3  1
1    ValueA1  2    ValueB2  1    1    ValueC3  1
1    ValueA1  2    ValueB2  1    2    ValueC3  1
1    ValueA1  2    ValueB2  1    3    ValueC3  1

如您所见，B 中的每个匹配项都会重复 3 次，以计算 C 获得的匹配数。并且，类似地，C 中的每个匹配项都会重复两次，因为这是 B 中的匹配项数量。当然，“最幸运”的是来自 A 的行，因为它重复了 2 × 3 = 6 次。这是您的笛卡尔连接。这也正是你的情况发生的情况。

不确定它是否被认为是典型的，但在这种情况下，我通常会通过连接表达式分别对每个表进行分组，然后连接结果集。您的查询将如下所示：

SELECT
  p.projectcode, 
  p.client, 
  p.project, 
  p.budget, 
  p.budget * 80 AS value, 
  h.hourslogged, 
  s.invoiced
FROM dbo.project p
  LEFT JOIN (
    SELECT
      projectcode,
      SUM(dbo.salesforce.value) AS invoiced
    FROM dbo.salesforce
    GROUP BY projectcode
  ) s ON p.projectcode = s.projectcode
  LEFT JOIN (
    SELECT
      projectcode,
      SUM(dbo.harvest.hours) AS hourslogged
    FROM dbo.harvest 
    GROUP BY projectcode
  ) h ON p.projectcode = h.projectcode

Answer 2

我建议避免混合右外连接和左外连接。
您的中心表是 Project，因此首先使用它。

SELECT  dbo.project.projectcode, 
        dbo.project.client, 
        dbo.project.project, 
        dbo.project.budget, 
        dbo.project.budget * 80 AS value, 
        SUM(dbo.harvest.hours) AS hourslogged, 
        SUM(dbo.salesforce.value) AS invoiced
FROM    dbo.project      
            LEFT OUTER JOIN dbo.salesforce
                ON dbo.salesforce.projectcode = dbo.project.projectcode 
            LEFT OUTER JOIN dbo.harvest 
                ON dbo.project.projectcode = dbo.harvest.projectcode
GROUP BY    dbo.project.projectcode, 
            dbo.project.project, 
            dbo.project.client, 
            dbo.project.budget

但错误来自 GROUP BY。您不必按要进行聚合的两个表进行分组，否则您的聚合将不好！