我有两表A,B
A表中: B表中:
A_CODE A_COUNT B_CODE B_COUNT
M0001 20 M0001 30
M0001 30
M0001 80
M0002 50
请问我如何得到:
CODE COUNT
M0001 100
M0002 50 并为这个结果创建一个视图呢?
A表中: B表中:
A_CODE A_COUNT B_CODE B_COUNT
M0001 20 M0001 30
M0001 30
M0001 80
M0002 50
请问我如何得到:
CODE COUNT
M0001 100
M0002 50 并为这个结果创建一个视图呢?
from a left join b on a_code=b_code
group by a_code
select a_code,sum(a_count)-COALESCE(sum(b_count),0)
from a left join b on a_code=b_code
group by a_code
(select a_code,sum(A_COUNT) as ma1 from aa1 group by a_code) a
left join
(select b_code,sum(b_COUNT) as ma2 from aa2 group by b_code) b
on a.a_code=b.b_code ) aq
a1.A_CODE as 'CODE',
sum(A_COUNT) - ifnull(a2.B_CODE,0) as 'COUNT'
from a1 left join a2 on a1.A_CODE = a2.B_CODE
group by a1.A_CODE;
只有分别GROUP BY,再连接。
as
select A_CODE,sum(A_COUNT) as COUNT
from
(
select A_CODE,A_COUNT from 表A
union all
select B_CODE,B_COUNT*-1 from 表B) t
group by A_CODE;
select a1.A_CODE as 'CODE', sum(A_COUNT) - ifnull(a2.B_COUNT,0) as 'COUNT' from a1 left join a2 on a1.A_CODE = a2.B_CODE group by a1.A_CODE;