SQ_LIST表
--------------------------------
物品ID 单据号 物品编号 申请数量
1 a01 0100100 1
2 a01 0100100 2
3 a01 0100100 1
--------------------------------
kcbgyp表
--------------------------------
物品ID 物品编号 在库数量
1 0100100 0
2 0100100 0
3 0100100 9
4 0100100 10
---------------------------------SELECT结果
---------------------------------
物品编号 在库数量 申请数量
0100100 19 4
---------------------------------单据号是主条件,看我的错误语句:
Select distinct SQ_LIST.wpbh,kcbgyp.pm,kcbgyp.gg,kcbgyp.dw,sum(kcbgyp.zksl) as zksl,sum(SQ_LIST.sqsl) as sqsl from kcbgyp,SQ_LIST where SQ_LIST.djh='"+djh+"' and kcbgyp.WPBH=SQ_LIST.WPBH group by sq_list.wpbh,kcbgyp.pm,kcbgyp.gg,kcbgyp.dw
--------------------------------
物品ID 单据号 物品编号 申请数量
1 a01 0100100 1
2 a01 0100100 2
3 a01 0100100 1
--------------------------------
kcbgyp表
--------------------------------
物品ID 物品编号 在库数量
1 0100100 0
2 0100100 0
3 0100100 9
4 0100100 10
---------------------------------SELECT结果
---------------------------------
物品编号 在库数量 申请数量
0100100 19 4
---------------------------------单据号是主条件,看我的错误语句:
Select distinct SQ_LIST.wpbh,kcbgyp.pm,kcbgyp.gg,kcbgyp.dw,sum(kcbgyp.zksl) as zksl,sum(SQ_LIST.sqsl) as sqsl from kcbgyp,SQ_LIST where SQ_LIST.djh='"+djh+"' and kcbgyp.WPBH=SQ_LIST.WPBH group by sq_list.wpbh,kcbgyp.pm,kcbgyp.gg,kcbgyp.dw
(select 物品编号,sum(在库数量) as 在库数量 from SQ_LIST group by 物品编号)t1
left outer join
(select 物品编号,sum(申请数量) as 申请数量 from kcbgyp group by 物品编号)t2
on t1.物品编号=t2.物品编号
(select 物品编号,sum(在库数量) as 在库数量 from SQ_LIST group by 物品编号)t1
left outer join
(select 物品编号,sum(申请数量) as 申请数量 from kcbgyp group by 物品编号)t2
on t1.物品编号=t2.物品编号
(select 物品编号,sum(在库数量) as 在库数量 from SQ_LIST group by 物品编号)t1
left outer join
(select 物品编号,sum(申请数量) as 申请数量 from kcbgyp group by 物品编号)t2
on t1.物品编号=t2.物品编号左右连接 学习中^
(select 物品编号,sum(在库数量) as 在库数量 from SQ_LIST group by 物品编号)t1
left outer join
(select 物品编号,sum(申请数量) as 申请数量 from kcbgyp group by 物品编号)t2
on t1.物品编号=t2.物品编号
where t1.单据号="a01"
select t1.*,t2.* from
(select 物品编号,pm,gg,pp,sum(在库数量) as 在库数量 from SQ_LIST group by 物品编号,pm,gg,pp)t1
left outer join
(select 物品编号,sum(申请数量) as 申请数量 from kcbgyp group by 物品编号)t2
on t1.物品编号=t2.物品编号
where t1.单据号="a01"