我在做仓库管理系统,现有进货表in,退货表out,借货表lend,还货表return进货表in igoods ihouse isum
笔记本 仓库一 10
铅笔 仓库二 10
黑板 仓库三 5
书 仓库一 10退货表out ogoods ohouse osum
笔记本 仓库一 2
黑板 仓库三 1借货表lend lgoods lhouse lsum
笔记本 仓库一 5
书 仓库一 6
铅笔 仓库二 2还货表return rgoods rhouse rsum
书 仓库一 3现在想生成一张库存表,就是把 进货表-退货表-借货表+还货表,结果如下:
库存表exist egoods ehouse esum
笔记本 仓库一 3
铅笔 仓库二 8
黑板 仓库三 4
书 仓库一 7
我不会写,请教高手!万分感谢!
笔记本 仓库一 10
铅笔 仓库二 10
黑板 仓库三 5
书 仓库一 10退货表out ogoods ohouse osum
笔记本 仓库一 2
黑板 仓库三 1借货表lend lgoods lhouse lsum
笔记本 仓库一 5
书 仓库一 6
铅笔 仓库二 2还货表return rgoods rhouse rsum
书 仓库一 3现在想生成一张库存表,就是把 进货表-退货表-借货表+还货表,结果如下:
库存表exist egoods ehouse esum
笔记本 仓库一 3
铅笔 仓库二 8
黑板 仓库三 4
书 仓库一 7
我不会写,请教高手!万分感谢!
解决方案 »
- 关于PC机自带串口和自己另配串口卡的区别?
- 如何在VB中调用帮助
- 由于事关重大,而且没有找到答案,所以重复提问:? 超级难的正则表达式问题-------VB中不能使用 零宽先行断言么 [VB
- 求助:如何将原先在韩文系统下的韩文VB程序转换成韩文系统下的简体中文VB程序???
- 困惑VB
- 如何在主窗体上显示一个子窗体,把子窗体显示在一定范围内?
- 請問高手
- 请问如何把三组数字合并在一齐,比如:a=2,b=3,c=4 ,合并之后为234,就像合并字符串的&一样,多谢
- ComboBox真的就不能变成平面的吗?
- 小草、洗海垂虹、女爱,请帮小弟回答今天9:30分,的关于报表打印的一个问题,怎么在报表中表示一个窗体的一个变量呢?
- 日期的语法错误 在查询表达式 '#2003-12-6 下午 03:04:37#' 中
- "12/25/2003"如何FORMAT成“2003-12-25”?
还是高手给写一下SQL语句吧!
谢谢!
注意,这只是区分货物名!如果同一物品还能放两个仓库,语句中还得加关于仓库的限定条件!
SELECT GOODS,HOUSE,SUM(HOUSE) FROM
((SELECT igoods AS GOODS,Ihouse AS HOUSE,SUM(TABLE1.isum) FROM 进货表IN GROUP BY igoods,Ihouse)
UNION
(SELECT ogoods AS GOODS,ohouse AS HOUSE,-SUM(Osum) FROM 退货表out GROUP BY ogoods,ohouse)
UNION
(SELECT lgoods AS GOODS,lhouse AS HOUSE,-SUM(Osum) FROM 借货表lend GROUP BY lgoods,lhouse)
UNION
(SELECT rgoods AS GOODS,rhouse AS HOUSE,SUM(Osum) FROM 还货表return GROUP BY rgoods,rhouse)) AS T
GROUP BY GOODS,HOUSE
((SELECT igoods AS GOODS,Ihouse AS HOUSE,SUM(TABLE1.isum) AS TSUM FROM 进货表IN GROUP BY igoods,Ihouse)
UNION
(SELECT ogoods AS GOODS,ohouse AS HOUSE,-SUM(Osum) AS TSUM FROM 退货表out GROUP BY ogoods,ohouse)
UNION
(SELECT lgoods AS GOODS,lhouse AS HOUSE,-SUM(Osum) AS TSUM FROM 借货表lend GROUP BY lgoods,lhouse)
UNION
(SELECT rgoods AS GOODS,rhouse AS HOUSE,SUM(Osum) AS TSUM FROM 还货表return GROUP BY rgoods,rhouse)) AS T
GROUP BY GOODS,HOUSE
select a,b,sum(c) as cc
from (
select igoods as a ,ihouse as b ,insum as c from in
union
select ogoods as a ,ohouse as b ,osum*(-1) as c from lend
union
select lgoods as a ,lhouse as b lsum*(-1) as c from out
union
select rgoods as a ,rhouse as b ,rsum as c from return
)
group by a,b