多个左连接与总和

2022-01-09 00:00:00 sql sum left-join php mysql

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

I'm trying to display in a table (with data tables plugin) informations with sum from 3 tables using Left Join in sql query. I succeeded to edit server-side query and display correct datas with first jointure between two tables (t1=...budget & t2=..budget_changes) using the following query : 

$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 个表时，总和结果是错误的.

But when I'm trying to connect 3 tables with Left joint query the sum results are wrong. 

$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

What's wrong with this query ? Many Thanks MT

推荐答案

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

The problem might be the fact due to a Cartesian result of your data and summations going on. Just to clarify, here is a simple query... I know I don't have it all, nor join columns perfect, this is just for clarification.

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

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

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

At the end, no problem... for a given year, you will get the totals from the second table. There are many records in table 2. Ex: Data

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

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

We probably agree on that at this point... Now, throw in another table that per year (or whatever), that too has multiple records per year...

Change_Orders
Yr     COAmt
2013   100
2013   120
2014   200
2014   220

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

And you add this in as a secondary left-join to your query, something like

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 每个存在于另一个表中的条目...类似于

However, since it is a Cartesian result... multiple rows per each join is taking the results TIMES each entry that exists in the other table... something like

Yr    AmtChange  COChangeAmt
2013  120         440
2014  180         840

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

To fix this, each individual table you are getting subtotals from should be handled on its own, and grouped by its own year so the subset returns only one row per context of data. Something like

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() 金额出现重复.

So, the sub-queries will each return only 1 record for the respective year being aggregated and thus prevent the duplicate in sum() amounts.

相关文章