MySQL的左联接总和两个表汇总 [英] mysql left join sum two tables with rollup

问题描述

我使用mysql语法进行堆栈，其中我的表收入包含值

i stack at mysql syntax where i have a table revenue with values

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

我想使用左联接联接这两个表，并汇总如下值:

I want to join this two table using left join and rollup the values like this :

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

这可能吗? 之前谢谢

推荐答案

以下是详细情况:

已提供数据:

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

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