Mysql查询求和和减法求和表

Question

我需要这个查询

表格基础:

Id name
1  ex1
2  ex2
3  ex3

表A

ExtId  p
1      10
1      20
2      40
3      10
3      10

表B

ExtId  p
1      10
1      5
3      5
3      5

表C

ExtId  p
1      10
2      10
2      20
3      40

输出:

表 A.p + 表 B.p - 表 C.p:

id   p
1    35
2    10
3   -10

Answer 1

select B1.id, coalesce(Q1.p,0) + coalesce(Q2.p,0) - coalesce(Q3.p,0) as p
from Base B1
left join 
    (
    select ExtId, sum(p) as p
    from TableA
    group by ExtID
    ) Q1
  on B1.id = Q1.ExtID
left join 
    (
    select ExtId, sum(p) as p
    from TableB
    group by ExtID
    ) Q2
  on B1.id = Q2.ExtID
left join 
    (
    select ExtId, sum(p) as p
    from TableC
    group by ExtID
    ) Q3
  on B1.id = Q3.ExtID