与SUM和GROUP的表连接不工作

I have three tables that I'm working with.

我有三个桌子要处理。

AccountingLine - Holds the generic account details
AccountingLine -包含一般的账户详细信息
Budget - Holds the budget data for each AccountingLine (Many rows per AccountingLine)
预算—保存每个帐户行(每个帐户行有许多行)的预算数据
Actual - Holds the actual cost data for each AccountingLine (Many rows per AccountingLine)
实际—保存每个account line的实际成本数据(每个account line有许多行)

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.

我试图在一个查询中获得结果，该查询将返回AccountingLine表中的所有行，并将预算和实绩表中的每个AccountingLine的金额相加。

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?

使用下面的SQL，总和对预算或实际数据不起作用。如果我删除一个连接和一个SUM函数，那么它将正确地计算单个连接表。很奇怪…有人在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`

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.

通过发布上面的语句，我希望看到accounting_line字段、每个accounting_line的预算金额之和和每个accounting_line的实际金额之和。

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

我到处找遍了，找不到多个函数的实例。非常感谢您的建议。

Josh

杰克

Table Data is below:

下面的表数据:

    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

3 个解决方案

#1

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.

一个连接重复另一个表中的每个匹配行。如果你有3行在3个表中并将它们连接在一起，最后得到9行。如果你加和，第二个和第三个表的每个和都是3倍的高。

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 个解决方案

#1

更多相关文章

随机推荐