3表联接与SUM和GROUP BY不起作用

问题描述：

我有三个正在使用的表.

I have three tables that I'm working with.

AccountingLine-保存通用帐户详细信息
预算-保留每个AccountingLine的预算数据(每个AccountingLine多行)
Actual-保留每个AccountingLine的实际成本数据(每个AccountingLine多行)

我试图在一个查询中获得结果，该查询将从AccountingLine表返回所有行，并从Budget和Actuals表中求和每个AccountingLine的金额.

I'm trying to get the results in a single query which will return ALL ROWS from the AccountingLine table, and SUM the Amounts for each AccountingLine from the Budget and Actuals table.

使用下面的SQL，SUM不适用于预算或实际数据.如果删除联接之一和SUM函数之一，则它将为单个联接表正确计算.真奇怪，有人在MySQL的三个或更多表上使用多个SUM函数遇到这个问题吗?

Using the SQL below, the SUM isn't working for the Budget or Actual data. If I remove one of the joins and one of the SUM functions then it calculates correctly for the single joined table. Very strange... anyone run across this with multiple SUM functions on three or more tables in MySQL?

SELECT  A.*, SUM(B.`amount`) AS BudgetAmount, SUM(ACT.`amount`) as ActualAmount 
FROM accounting_line A 
LEFT JOIN budget B ON B.accounting_line_id = A.accounting_line_id
LEFT JOIN actual ACT ON  ACT.accounting_line_id = A.accounting_line_id
GROUP BY A.`accounting_line_id`

通过发布上述声明，我希望看到accounting_line字段，每个accounting_line的预算金额之和和每个accounting_line的实际金额之和.

By issuing the statement above, I'd expect to see the accounting_line fields, the SUM of the Budget amounts for each accounting_line and the SUM of the Actual amounts for each accounting_line.

我已经进行了全面搜索，找不到多个SUM函数的实例.非常感谢您的任何建议.

I've searched all over and can't find an instance of multiple SUM functions. Thanks so much for any advice.

乔什

表格数据如下:

    Table: AccountingLine

      act_line_id   department
    ----------------------------------  
                1   Sales   
                2   HumanResources


Table: Budget


        budget_id   actg_line_id     amount
    ----------------------------------------------  
                1              1          3500.00
                2              2          5000.00
                3              2          15000.00

Table: Actual


        actual_id   actg_line_id     amount
    ----------------------------------------------  
                1              1        1000.00
                2              2         500.00
                3              2        9000.00

答

join重复另一个表中的每个匹配行.因此，如果在三个表中有3行并将它们连接在一起，则最终将有9行.如果求和，第二张和第三张表中的每个和都高3倍.

A join repeats each matching row in the other table. So if you have 3 rows in three tables and join them together, you end up with 9 rows. If you sum, each sum from the second and third table is 3x too high.

一种解决方案是对子查询求和，以便join仅找到一行:

One solution is to sum in a subquery, so that the join only finds one row:

SELECT   A.*
,        B.SumAmount as BudgetAmount
,        ACT.SumAmount as ActualAmount 
FROM     accounting_line A 
LEFT JOIN 
        (
        select  accounting_line_id
        ,       sum(amount) as SumAmount
        from    budget
        group by
                accounting_line_id
        ) as B
ON      B.accounting_line_id = A.accounting_line_id
LEFT JOIN 
        (
        select  accounting_line_id
        ,       sum(amount) as SumAmount
        from    actual
        group by
                accounting_line_id
        ) as ACT 
ON      ACT.accounting_line_id = A.accounting_line_id

3表联接与SUM和GROUP BY不起作用

相关推荐