select a.productName, a.barcode, sum((case when flag=2 then -1 else 1 end)*sellnum) from ( select * from a union allselect * from b ) agroup by a.productName, a.barcode
--进货和退货 tb1 --销售和退货 tb2select pname,bcode,sum(innum) as total_num from( select pname,bcode,innum from tb1 where flag = 1 union all select pname,bcode,-innum from tb1 where flag = 2 union all select pname,bcode,-innum from tb2 where flag = 1 union all select pname,bcode,innum from tb2 where flag = 2 ) t group by pname,bcode--这个是统计的方法,如果要高效的话建议可以使用视图!相关的索引要创建,减少表扫描的次数,或者可以利用临时表。
表先建分区,按flag。
再按flag、productname汇总,合并出结果。
(
select * from a
union allselect * from b
) agroup by a.productName, a.barcode
--进货和退货 tb1
--销售和退货 tb2select pname,bcode,sum(innum) as total_num
from(
select pname,bcode,innum from tb1 where flag = 1
union all
select pname,bcode,-innum from tb1 where flag = 2
union all
select pname,bcode,-innum from tb2 where flag = 1
union all
select pname,bcode,innum from tb2 where flag = 2
) t
group by pname,bcode--这个是统计的方法,如果要高效的话建议可以使用视图!相关的索引要创建,减少表扫描的次数,或者可以利用临时表。