table A
名称 数量
a 3
a 4
b 9
b 4
c 5table B
名称 数量
a 7
b 6
b 9
d 4
e 2如何计算用table A数量减去table B并去掉0的结果,如下显示
名称 A数量 B数量 A-B
b 13 15 -2
c 5 0 5
d 0 4 -4
e 0 2 -2
名称 数量
a 3
a 4
b 9
b 4
c 5table B
名称 数量
a 7
b 6
b 9
d 4
e 2如何计算用table A数量减去table B并去掉0的结果,如下显示
名称 A数量 B数量 A-B
b 13 15 -2
c 5 0 5
d 0 4 -4
e 0 2 -2
sum(if(bz='b',数量,0)) as A数量 ,
sum(if(bz='a',数量,-1*数量)) as `A-B`
from (
select *,'a' as bz from a
union all
select *,'b' as bz from b
) tt group by 名称
from (xxxxxxx)
where A<>0 or B <>0
sum(if(bz='b',数量,0)) as A数量 ,
sum(if(bz='a',数量,-1*数量)) as `A-B`
from (
select *,'a' as bz from a
union all
select *,'b' as bz from b
) tt group by 名称 HAVING sum(if(bz='a',数量,-1*数量))>0
sum(if(bz='b',数量,0) as A数量,
sum(IF(bz='a',数量,-1*数量) as `A-B`
FROM(
select *,'a' as bz from a
union all
select *,'b' as bz from b
)t
group by 名称
having sum(if(bz='a',数量,-1*数量))>0
select 名称,数量 as A数量, 0 as B数量 from a
union all
select 名称,0 as A数量, 数量 as B数量 from b
) t
group by 名称
having sum(A数量)<>sum(B数量)