php - 多个左连接与总和

Question

我正在尝试在一个表(带有数据表插件)中显示信息，其中包含来自 3 个表的总和，在 sql 查询中使用 Left Join。我成功编辑服务器端查询并使用以下查询在两个表 (t1=...budget & t2=..budget_changes) 之间的第一个连接处显示正确的数据:

$year=date('Y');

$sQuery = "SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns)).", 

IFNULL(SUM(t2.change_amount),0) AS operation_changes,
(t1.operation_BP+IFNULL(SUM(t2.change_amount),0)) AS operation_total 
FROM budget AS t1 

LEFT JOIN wp_dri_budget_changes AS t2 ON t2.change_year_operation=t1.operation_year_number 

WHERE t1.operation_year=".$year." AND t1.operation_active=1 $sWhere

GROUP BY operation_year_number, change_year_operation $sOrder $sLimit";

但是当我尝试使用左联合查询连接 3 个表时，求和结果是错误的。

$year=date('Y');

$sQuery = "SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns)).",

IFNULL(SUM(t2.change_amount),0) AS operation_changes,

(t1.operation_BP+IFNULL(SUM(t2.change_amount),0)) AS operation_total,

IFNULL(SUM(t3.expense_enga_amount),0) AS operation_consommation

FROM budget AS t1

LEFT JOIN wp_dri_budget_changes AS t2 ON t2.change_year_operation=t1.operation_year_number

LEFT JOIN wp_dri_budget_expenses AS t3 ON t3.expense_year_operation=t1.operation_year_number 

WHERE t1.operation_year=".$year." AND t1.operation_active=1 $sWhere GROUP BY operation_year_number, change_year_operation, expense_year_operation $sOrder $sLimit";

这个查询有什么问题？非常感谢MT

Answer 1

问题可能是由于您的数据和求和的笛卡尔结果所致。澄清一下，这是一个简单的查询...我知道我没有全部，也没有完美地连接列，这只是为了澄清。

此外，我知道我已经缩写了列和别名，以便简化阅读和理解您可能遇到的概念。

Select
      t1.yr,
      sum( t2.Amt ) as AmtChange
   FROM 
      budget AS t1
         LEFT JOIN Budget_Changes AS t2
            on t1.yr = t2.Yr

最后，没问题...对于给定年份，您将从第二个表中获得总计。表2中有很多条记录。例:数据

Budget
Yr
2013
2014

Budget_Changes
Yr    Amt
2013  10
2013  20
2013  30
2014  40
2014  50

Your results would be
Yr    AmtChange
2013  60
2014  90

在这一点上我们可能同意...现在，放入另一个每年(或其他)表，每年也有多个记录...

Change_Orders
Yr     COAmt
2013   100
2013   120
2014   200
2014   220

然后您将其作为辅助左连接添加到您的查询中，例如

Select
      t1.yr,
      sum( t2.Amt ) as AmtChange,
      sum( t3.COAmt ) as COAmtChange
   FROM 
      budget AS t1
         LEFT JOIN Budget_Changes AS t2
            on t1.yr = t2.Yr
         LEFT JOIN Change_Orders AS t3
            on t1.yr = t3.Yr


Your might expect the results to be
Yr    AmtChange  COChangeAmt
2013  60         220
2014  90         420

但是，因为它是一个笛卡尔结果...每个连接的多行取结果 TIMES 存在于另一个表中的每个条目...类似

Yr    AmtChange  COChangeAmt
2013  120         440
2014  180         840

要解决此问题，您从中获取小计的每个单独的表都应单独处理，并按其自身的年份分组，以便子集在每个数据上下文中仅返回一行。有点像

Select
      t1.yr,
      t2.AmtChange,
      t3.COAmtChange
   FROM 
      budget AS t1
         LEFT JOIN ( select BC.Yr, sum( BC.Amt ) as AmtChange
                        from Budget_Changes BC
                        group by BC.Yr ) t2
            on t1.yr = t2.Yr
         LEFT JOIN ( select CO.Yr, sum( CO.COAmt ) as COAmtChange
                        from Change_Orders CO
                        group by CO.Yr ) AS t3
            on t1.yr = t3.Yr

因此，对于聚合的相应年份，每个子查询将仅返回 1 条记录，从而防止 sum() 金额重复。