除了名称不同,其它完全一样,可以直接照搬{:soso_e127:} :- SELECT T1.日期,T2.物料代码,T1.物料名称,T1.规格型号,T2.数量+IIF(T1.期初,T1.期初,0) AS 期初数量,T1.入库,T1.出库,数量+结存 AS 结存数量 FROM
- (SELECT A.日期,A.物料长代码,A.物料名称,A.规格型号,SUM(IIF(B.入库,B.入库,0))-SUM(IIF(B.出库,B.出库,0)) AS 期初,期初+IIF(A.入库,A.入库,0)-IIF(A.出库,A.出库,0) AS 结存,A.入库,A.出库 FROM
- (SELECT 日期,物料长代码,物料名称,规格型号,SUM(入库) AS 入库,SUM(出库) AS 出库 FROM
- (SELECT 日期,物料长代码,物料名称,规格型号,数量 AS 入库,0 AS 出库 FROM [入库$]
- UNION ALL
- SELECT 日期,产品长代码,产品名称,规格型号,0,数量 FROM [出库$])
- GROUP BY 日期,物料长代码,物料名称,规格型号)A
- LEFT JOIN
- (SELECT 日期,物料长代码,物料名称,规格型号,SUM(入库) AS 入库,SUM(出库) AS 出库 FROM
- (SELECT 日期,物料长代码,物料名称,规格型号,数量 AS 入库,0 AS 出库 FROM [入库$]
- UNION ALL
- SELECT 日期,产品长代码,产品名称,规格型号,0,数量 FROM [出库$])
- GROUP BY 日期,物料长代码,物料名称,规格型号)
- B
- ON A.物料长代码=B.物料长代码 AND A.日期>B.日期
- GROUP BY A.日期,A.物料长代码,A.物料名称,A.规格型号,A.入库,A.出库)T1
- RIGHT JOIN
- (SELECT 物料代码,SUM(数量) AS 数量 FROM
- (SELECT 物料代码,数量 FROM [期初$] UNION ALL SELECT 物料长代码,0 FROM [入库$])
- WHERE 物料代码 NOT IN ("(空白)","总计")
- GROUP BY 物料代码)T2
- ON T1.物料长代码=T2.物料代码
- ORDER BY T2.物料代码,T1.日期
复制代码
|