|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
代码是长了,能得到所要的格式就行了,我是通过MQ建的透视表,代码发给你
"select a.月,b.物资编码,b.物资名称,b.结存数量+iif(a.出库数量 is null,0,a.出库数量)-iif(a.入库数量 is null,0,a.入库数量) as 月初数量,b.结存金额+iif(a.出库金额 is null,0,a.出库金额)-iif(a.入库金额 is null,0,a.入库金额) as 月初金额,a.入库数量,a.入库金额,a.出库数量,a.出库金额,b.结存数量,b.结存金额 from (select 月,物资编码,物资名称,sum(入数量) as 入库数量,sum(入金额) as 入库金额,sum(出数量) as 出库数量,sum(出金额) as 出库金额 from (select month(日期) as 月,物资编码,物资名称,数量 as 入数量,金额 as 入金额,0 as 出数量,0 as 出金额 from [入库$] union all select month(日期) as 月,物资名称,物资名称,0 as 入数量,0 as 入金额,数量 as 出数量,金额 as 出金额 from [出库$]) where 月=? group by 月,物资编码,物资名称)a RIGHT JOIN (select 月,物资编码,物资名称,sum(初数量)+sum(入数量)-iif(sum(出数量) is null,0,sum(出数量)) as 结存数量,sum(初金额)+sum(入金额)-iif(sum(出金额) is null,0,sum(出金额)) as 结存金额 from (select month(日期) as 月,物资编码,物资名称,数量 as 初数量,金额 as 初金额,0 as 入数量,0 as 入金额,0 as 出数量,0 as 出金额 from [期初$] union all select month(日期) as 月,物资编码,物资名称,0 as 初数量,0 as 初金额,数量 as 入数量,金额 as 入金额,0 as 出数量,0 as 出金额 from [入库$] union all select month(日期) as 月,物资编码,物资名称,0 as 初数量,0 as 初金额,0 as 入数量,0 as 入金额,数量 as 出数量,金额 as 出金额 from [出库$]) where 月 <=? group by 月,物资编码,物资名称)b on a.物资编码=b.物资编码 and a.物资名称=b.物资名称" |
|