OK 解决了!不过你要建两个测试表:
测试表:
dtIn (入库表
id : char(1)
inDate : date
inValue: number(4)
value:
1 2002-8-1 100
1 2002-8-10 300
1 2002-8-12 300
dtOut 出库表
id : char(1)
outdate: date
outValue: number(4)
value:
1 2002-8-3 50
1 2002-8-10 150
1 2002-8-13 100
1 2002-8-14 200
测试时货号固定为 id='1'
结果:
2002-08-02 100 0 100
2002-08-03 0 50 50
2002-08-10 300 150 200
2002-08-12 300 0 500
2002-08-13 0 100 400
2002-08-14 0 200 200
select a.mdate,a.invalue,a.outvalue,b.value
from
(
select rownum as num,a.mdate,a.invalue,a.outvalue
from
(
SELECT mdate, SUM(invalue) AS invalue, SUM(outvalue) AS outvalue, SUM(invalue)
- SUM(outvalue) AS TempCount
FROM (SELECT id,outDate AS mdate, 0 AS invalue, SUM(outValue) AS outvalue
FROM dtOut
where id='1'
GROUP BY outDate,id
UNION
SELECT id,indate AS mdate, SUM(inValue) AS idvalue, 0 AS outvalue
FROM dtIN
where id='1'
GROUP BY indate,id) a
GROUP BY mdate
) a
) a,
(
select rownum as num,a.mdate,a.invalue,a.outvalue,
(
select sum(invalue)-sum(outvalue) as value
from
(
select mdate,invalue,outvalue,rownum as num
from
(
SELECT mdate, SUM(invalue) AS invalue, SUM(outvalue) AS outvalue, SUM(invalue)
- SUM(outvalue) AS TempCount
FROM (SELECT id,outDate AS mdate, 0 AS invalue, SUM(outValue) AS outvalue
FROM dtOut
where id='1'
GROUP BY outDate,id
UNION
SELECT id,indate AS mdate, SUM(inValue) AS idvalue, 0 AS outvalue
FROM dtIN
where id='1'
GROUP BY indate,id) a
GROUP BY mdate
)
) b
where b.num<=c.num
) as value
from
(
SELECT mdate, SUM(invalue) AS invalue, SUM(outvalue) AS outvalue, SUM(invalue)
- SUM(outvalue) AS TempCount
FROM (SELECT id,outDate AS mdate, 0 AS invalue, SUM(outValue) AS outvalue
FROM dtOut
where id='1'
GROUP BY outDate,id
UNION
SELECT id,indate AS mdate, SUM(inValue) AS idvalue, 0 AS outvalue
FROM dtIN
where id='1'
GROUP BY indate,id) a
GROUP BY mdate
) a,
(
select rownum as num
from
(
SELECT mdate
FROM (SELECT id,outDate AS mdate, 0 AS invalue, SUM(outValue) AS outvalue
FROM dtOut
where id='1'
GROUP BY outDate,id
UNION
SELECT id,indate AS mdate, SUM(inValue) AS idvalue, 0 AS outvalue
FROM dtIN
where id='1'
GROUP BY indate,id) a
GROUP BY mdate
) a
) c
where rownum=c.num
) b
where a.num=b.num