有两个表:
表一 AAA
种类mc 库存总量s1
A 997
B 1234
表二 BBB
种类mc 出库数量s1
A 105
A 213
B 116
B 211
B 303
用一条SQL语句求出A,B各剩下多少?
两种方法如下:
A:
select 库存总量s1-sum(出库数量s1) from BBB,AAA where BBB.种类mc=AAA.种类mc group by BBB.种类mc,库存总量s1
结果: kind stock A 679 B 604
B:
select a.mc,a.s1-b.q_out
from aaa a,(
select mc ,sum(s1) as q_out
from bb group by mc
)b
where a.mc=b.mc