1.有两张表,表一 AAA
种类mc | 库存总量s1 |
A | 997 |
B | 1234 |
表二:BBB
种类mc | 出库总量s2 |
A | 105 |
A | 213 |
B | 116 |
B | 211 |
B | 303 |
select AAA.种类mc,(sum(AAA.s1)-sum(BBB.s2)) as '剩余数量' from AAA inner join BBB on AAA.种类mc=BBB.种类mc group by AAA.种类mc
2.存在出入库明细表T
单据号 | 单据类型 | 操作日期 | 商品 | 数量 |
001 | 入库 | 2007-01-01 | A | 32 |
002 | 入库 | 2007-03-02 | B | 15 |
003 | 入库 | 2007-03-02 | B | 10 |
004 | 出库 | 2007-03-19 | A | 2 |
004 | 出库 | 2007-04-10 | B | 3 |
商品 | 起初数量 | 统计期入库数量 | 统计期出库数量 | 结存数量 |
A | 32 | 0 | 2 | 30 |
B | 0 | 25 | 25 | 25 |
select A.商品,(sum(A.数量)-sum(B.数量)) as '期初数量',sum(C.数量) as '统计期入库数量',sum(D.数量) as '统计期出库数量',(sum(E.数量)-sum(F.数量)) as '结存数量' from T as A left join T as B on A.单据号=B.单据号 left join T as C on B.单据号=C.单据号 left join T as D on C.单据号=D.单据号 left join T as E on D.单据号=E.单据号 left join T as F on E.单据号=F.单据号 where A.单据类型='入库' and A.操作日期<2007-03-01 and B.单据类型='出库' and B.操作日期<2007-03-01 and C.单据类型='入库' and C.操作日期>2007-3-01 and C.操作日期<2007-03-31 and D.单据类型='出库’ and D.操作日期>2007-03-01 and D.操作日期<2007-03-31 and E.单据类型='入库' and F.单据类型='出库' group by A.商品