多个左加和 [英] Multiple Left Join with sum

问题描述

我正在尝试在表(带有数据表插件)中显示信息，并使用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";

但是当我尝试用Left联合查询连接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

但是，由于它是笛卡尔结果，所以每个联接多行将结果乘以另一个表中存在的每个条目的时间...类似

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.

这篇关于多个左加和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！