'清空入庫統計(rktj)，出庫統計(cktj)，統計(tj)三個表中資料
sql = "DELETE * From rktj WHERE 1=1"
sql = "DELETE * From cktj WHERE 1=1"
sql = "DELETE * From tj WHERE 1=1"
'插入資料到三個表中
"INSERT INTO rktj ( pm, xh, lsj, rksl ) SELECT rk.pm, rk.xh, rk.lsj, Sum(rk.rksl) AS 入庫 From rk GROUP BY rk.pm, rk.xh, rk.lsj HAVING Sum(rk.[rksl])"
"INSERT INTO cktj ( pm, xh, lsj, cksl ) SELECT pm, xh, lsj, Sum(cksl) AS 出庫 From ck GROUP BY pm, xh, lsj HAVING (((Sum(ck.[cksl]))<>False));"
"INSERT INTO tj ( pm, xh, lsj, rksl, cksl, jy ) SELECT rktj.pm, rktj.xh, rktj.lsj, rktj.rksl AS 入庫, cktj.cksl AS 出庫, rktj.rksl-cktj.cksl AS 結余 From rktj, cktj WHERE rktj.pm=cktj.pm;"
sql = "select * from tj"
這樣得出的結果是庫存不全，只有出過庫的才被顯示出來了。。如何讓他沒有出過庫的也在里面同時顯示出來？
uj5u.com熱心網友回復：
--用left join 就可以了
INSERT INTO tj ( pm, xh, lsj, rksl, cksl, jy )
SELECT rktj.pm, rktj.xh, rktj.lsj, rktj.rksl AS 入庫, cktj.cksl AS 出庫, rktj.rksl-cktj.cksl AS 結余
FROM rktj LEFT join cktj on rktj.pm=cktj.pm;
uj5u.com熱心網友回復：
欄位說明：pm品名 xh型號 lsj零售價 rksl入庫數量 cksl出庫數量 jy結余表結構：
入庫表中欄位pm xh lsj rksl
出庫表中pm xh lsj cksl
要求結果：
統計結果插入統計表中pm xh lsj rksl cksl jy
uj5u.com熱心網友回復：
大佬這個不行啊。。執行 的時候要求輸入rksl和cksl....完成不了
uj5u.com熱心網友回復：
從sql server的角度看,完全沒問題的,你這個要求輸入rksl和cksl,這就不知道了,你的是啥資料庫uj5u.com熱心網友回復：
accessuj5u.com熱心網友回復：
access這個語法也沒問題呀,怎么會要輸入入庫數量和出庫數量呢
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/214425.html
標籤：應用實例
下一篇：sublime