主题：[求助]统计筛选出入库的物料数量SQL语句

select 物料编号, sum(入库)-sum(出库)  as 库存 f rom {出入库明细表} group by 物料编号

select 物料全编号, (sum(iif(转出仓 is null,数量,0)) - sum(iif(转入仓 is null,数量,0))) as 库存 f rom (select * f rom {主表} a inner join {明细} b on a.物料操作编号 = b.物料操作编号) group by 物料全编号

 再不行，请上传具体例子

select 物料全编号, 到货编号, (sum(iif(转出仓 is null,数量,0)) - sum(iif(转入仓 is null,数量,0))) as 库存 f rom (select * f rom {物料出入库主表} a inner join {物料操作明细表} b on a.物料操作编号 = b.物料操作编号) group by 物料全编号,到货编号

 sqlServer数据库就改一下iif改成case when

 

 参考语法 http://blog.sina.com.cn/s/blog_61fbf8d10100lian.html

 

select 物料全编号, 到货编号, (sum(case when 转出仓 is null then 数量 else 0 end)) - sum(case when 转入仓 is null then 数量 then 0 end))) as 库存 f rom (select * f rom {物料出入库主表} a inner join {物料操作明细表} b on a.物料操作编号 = b.物料操作编号) group by 物料全编号,到货编号

select 物料全编号, 到货编号, (sum(case when 转出仓 is null then 数量 else 0 end)) - sum(case when 转入仓 is null then 数量 then 0 end))) as 库存 f rom (select * f rom {物料出入库主表} a inner join {物料操作明细表} b on a.物料操作编号 = b.物料操作编号) As aaa group by 物料全编号,到货编号

select 物料全编号, 到货编号, (sum(case when 转出仓 is null then 数量 else 0 end) - sum(case when 转入仓 is null then 数量 else 0 end)) as 库存 f rom (select a.转入仓, a.转出仓, b.* f rom {物料出入库主表} a inner join {物料操作明细表} b on a.物料操作编号 = b.物料操作编号) as aaa group by 物料全编号,到货编号