|
1、将表中有关数量的字段改为数字型,日期字段改为日期型(RQ);
2、TRY:
SELECT sp, rq1, qcs1+期初数 AS qcs, rk, ck, jc+期初数 AS kcs
FROM (SELECT *,(select sum(rk-ck) from
(SELECT sp, rq1, rk, ck
FROM (SELECT IIF(ISNULL(A.商品),B.商品,A.商品) AS SP,IIF(ISNULL(A.RQ),B.RQ,A.RQ) AS RQ1,
IIF(ISNULL(出库数量),0,出库数量) AS CK,IIF(ISNULL(入库数量),0,入库数量) AS RK,
* FROM (
SELECT *
FROM 出库 A LEFT JOIN 入库 B ON A.商品=B.商品 AND A.RQ=B.RQ
UNION
SELECT *
FROM 出库 A RIGHT JOIN 入库 B ON A.商品=B.商品 AND A.RQ=B.RQ)
ORDER BY 1,A.RQ,B.RQ)
) AS BG
where Ya.sp=BG.sp and Ya.RQ1>=BG.RQ1) as jc,
nz((select sum(rk-ck) from
(SELECT sp, rq1, rk, ck
FROM (SELECT IIF(ISNULL(A.商品),B.商品,A.商品) AS SP,IIF(ISNULL(A.RQ),B.RQ,A.RQ) AS RQ1,
IIF(ISNULL(出库数量),0,出库数量) AS CK,IIF(ISNULL(入库数量),0,入库数量) AS RK,
* FROM (
SELECT *
FROM 出库 A LEFT JOIN 入库 B ON A.商品=B.商品 AND A.RQ=B.RQ
UNION
SELECT *
FROM 出库 A RIGHT JOIN 入库 B ON A.商品=B.商品 AND A.RQ=B.RQ)
ORDER BY 1,A.RQ,B.RQ)
)
where Ya.sp=sp and Ya.RQ1>RQ1),0) as qcs1 from
(SELECT sp, rq1, rk, ck
FROM (SELECT IIF(ISNULL(A.商品),B.商品,A.商品) AS SP,IIF(ISNULL(A.RQ),B.RQ,A.RQ) AS RQ1,
IIF(ISNULL(出库数量),0,出库数量) AS CK,IIF(ISNULL(入库数量),0,入库数量) AS RK,
* FROM (
SELECT *
FROM 出库 A LEFT JOIN 入库 B ON A.商品=B.商品 AND A.RQ=B.RQ
UNION
SELECT *
FROM 出库 A RIGHT JOIN 入库 B ON A.商品=B.商品 AND A.RQ=B.RQ)
ORDER BY 1,A.RQ,B.RQ)
)
Ya) AS b LEFT JOIN 商品表 AS A ON a.商品=b.sp
ORDER BY SP,RQ1
[此贴子已经被作者于2006-10-10 16:17:05编辑过]
|
|