SQL两表数据存入新表（入库，出库，库存）

[llSnakesss]

'清空入库统计(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"

这样得出的结果是库存不全，只有出过库的才被显示出来了。。如何让他没有出过库的也在里面同时显示出来？

[ダ雨夹雪リ]

--用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;

[llSnakesss]

字段说明：pm品名 xh型号 lsj零售价 rksl入库数量 cksl出库数量 jy结余 表结构： 入库表中字段pm xh lsj rksl 出库表中pm xh lsj cksl 要求结果： 统计结果插入统计表中pm xh lsj rksl cksl jy

[llSnakesss]

引用 1 楼 雨夹雪 的回复:

--用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;

大佬这个不行啊。。执行 的时候要求输入rksl和cksl....完成不了

[ダ雨夹雪リ]

从sql server的角度看,完全没问题的,你这个要求输入rksl和cksl,这就不知道了,你的是啥数据库

[llSnakesss]

access

[ダ雨夹雪リ]

引用 5 楼 llSnakesss 的回复:

access

access这个语法也没问题呀,怎么会要输入入库数量和出库数量呢