MySQL连接和总和成倍增加 [英] Mysql join and sum is doubling result

问题描述

我的收入表为

title_id     revenue    cost
   1            10        5
   2            10        5
   3            10        5
   4            10        5
   1            20        6                    
   2            20        6
   3            20        6
   4            20        6

当我执行此查询时

SELECT SUM(revenue),SUM(cost)
FROM revenue
GROUP BY revenue.title_id

它产生结果

title_id     revenue    cost
   1            30        11
   2            30        11
   3            30        11
   4            30        11

没关系，现在我想将求和结果与另一个具有这种结构的表合并

which is ok, now i want to combine sum result with another table which has structure like this

title_id     interest    
   1            10        
   2            10        
   3            10        
   4            10        
   1            20                            
   2            20        
   3            20        
   4            20        

当我执行像这样的聚合函数加入时

when i execute join with aggregate function like this

SELECT SUM(revenue),SUM(cost),SUM(interest)
FROM revenue
LEFT JOIN fund ON revenue.title_id = fund.title_id
GROUP BY revenue.title_id,fund.title_id      

将结果翻倍

title_id     revenue    cost    interest
   1            60        22       60
   2            60        22       60
   3            60        22       60
   4            60        22       60

我不明白为什么将它翻倍，请帮助

I can't understand why is it double it,please help

推荐答案

它翻倍，因为您在基金和收入表中重复了标题.这会将它匹配的记录数相乘.如果删除聚合函数并查看原始数据，这很容易看到. 请参见此处

Its doubling because you have title repeated in fund and revenue tables. This multiplies the number of records where it matches. This is pretty easy to see if you remove the aggregate functions and look at the raw data. See here

解决此问题的方法是创建聚合的内联视图并加入这些结果.

The way to get around this is to create inline views of your aggregates and join on the those results.

SELECT R.title_id, 
       R.revenue, 
       R.cost, 
       F.interest 
FROM   (SELECT title_id, 
               Sum(revenue) revenue, 
               Sum(cost)    cost 
        FROM   revenue 
        GROUP  BY revenue.title_id) r 
       LEFT JOIN (SELECT title_id, 
                         Sum(interest) interest 
                  FROM   fund 
                  GROUP  BY title_id) f 
              ON r.title_id = F.title_id 

输出

| TITLE_ID | REVENUE | COST | INTEREST |
----------------------------------------
|        1 |      30 |   11 |       30 |
|        2 |      30 |   11 |       30 |
|        3 |      30 |   11 |       30 |
|        4 |      30 |   11 |       30 |

演示

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