mysql left join sum 两个表与汇总

Question

我在 mysql 语法中堆栈，其中我有一个带有值的表收入

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

然后我有值表基金

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

我想使用左连接连接这两个表并像这样汇总值:

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 with rollup) r 
   LEFT JOIN (SELECT title_id, 
                     Sum(interest) interest 
              FROM   fund 
              GROUP  BY title_id with rollup) 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
Total  120  44  null

但我想要的输出是:

title_id | revenue | cost | interest
1   30  11  30
2   30  11  30
3   30  11  30
4   30  11  30
Total  120  44  120

这可能吗？先谢谢

Answer 1

详细场景如下:

给定数据:

select a.title_id,  sum(revenue), sum(cost),sum(interest) from
(select a.title_id,  sum(revenue) as revenue, sum(cost) as cost from
(select  1 title_id, 10 revenue , 5 cost UNION all
select 2,   10,  5 UNION all
select 3,   10,  5 UNION all
select 4,   10,  5 UNION all
select 1,   20,  6 UNION all
select 2,   20,  6 UNION all
select 3,   20,  6 UNION all
select 4,   20,  6) as a
GROUP BY title_id) as a

left JOIN

(select title_id, sum(interest) as interest from
(select 1 as title_id, 10 as interest UNION all
select 2,   10 UNION all
select 3,  10 UNION all
select 4,   10 UNION all
select 1,  20 UNION all
select 2,  20 UNION all
select 3, 20 UNION all
select 4,  20) as b
GROUP BY title_id ) as b
on a.title_id = b.title_id 
GROUP BY a.title_id
with ROLLUP

结果:

1   30  11  30
2   30  11  30
3   30  11  30
4   30  11  30
    120 44  120

最终查询结构:

select a.title_id,  sum(revenue), sum(cost),sum(interest) from
(select a.title_id,  sum(revenue) as revenue, sum(cost) as cost from
(select * from revenue) as a
GROUP BY title_id) as a

left JOIN

(select title_id, sum(interest) as interest from
(select * from fund) as b
GROUP BY title_id ) as b
on a.title_id = b.title_id 
GROUP BY a.title_id
with ROLLUP