那更简单了 一个函数就可以了 select t1.id,t1.materialid,t1.needAmount, lag(totalamount-needAmount,1,totalamount) over(partition by t1.materialid order by t1.id) c_Amount from t_orderticket t1,t_storeitem t2 where t1.materialid=t2.materialid id materialid needAmount c_Amount ------------------------------------------- 1 1 1001 100 10000 2 2 1001 250 9900 3 3 1001 150 9750 4 4 1002 200 20000 5 5 1002 100 19800 6 6 1003 300 30000
create table t_storeitem as select 1 id,1001 materialid,10000 totalAmount from dual union select 2,1002,20000 from dual union select 3,1003,30000 from dual;create table t_orderticket as select 1 id,1001 materialid,100 needAmount from dual union select 2,1001,250 from dual union select 3,1001,150 from dual union select 3,1002,200 from dual union select 3,1002,150 from dual union select 3,1003,300 from dual;select rownum as orderticketid,a.materialid,lag(a.totalAmount-b.needAmount,1,a.totalAmount) over(partition by a.materialid order by a.id) as remainAmount from t_storeitem a,t_orderticket b where a.materialid=b.materialid
不是直接拿总数减去上一条的需求数,而是总数减相同materialid需求数的总和(不包括本单的)
这样应该可以了吧?create table t_storeitem (id number(5),materialid number(4),totalAmount number(10)); insert into t_storeitem values (1,1001,10000); insert into t_storeitem values (2,1002,20000); insert into t_storeitem values (3,1003,30000);create table t_orderticket (id number(5),materialid number(4),needAmount number(10)); insert into t_orderticket values (1,1001,100); insert into t_orderticket values (2,1001,250); insert into t_orderticket values (3,1001,150); insert into t_orderticket values (4,1002,200); insert into t_orderticket values (5,1002,100); insert into t_orderticket values (6,1003,300); insert into t_orderticket values (7,1003,600); insert into t_orderticket values (8,1001,1000); insert into t_orderticket values (9,1001,800); commit;select t1.id,t1.materialid,t1.needAmount, sum(decode(rn,1,t2.totalAmount,-(select needAmount from (select needAmount,materialid,id from t_orderticket order by id desc) tt where tt.materialid=t1.materialid and tt.id<t1.id and rownum<2))) over(partition by t1.materialid order by t1.id) c_Amount from (select id,materialid,needAmount, row_number() over(partition by materialid order by id) rn from t_orderticket) t1,t_storeitem t2 where t1.materialid=t2.materialid id materialid needAmount c_Amount ------------------------------------------- 1 1 1001 100 10000 2 2 1001 250 9900 3 3 1001 150 9650 4 8 1001 1000 9500 5 9 1001 800 8500 6 4 1002 200 20000 7 5 1002 100 19800 8 6 1003 300 30000 9 7 1003 600 29700
貌似这样可以?select t1.id,t1.materialid,t1.needAmount, nvl(totalamount-(select sum(needAmount) from t_orderticket a where a.materialid=t1.materialid and a.id<t1.id),totalamount) c from t_orderticket t1,t_storeitem t2 where t1.materialid=t2.materialid order by t1.materialid,t1.id
楼上的那个,就很直观。 也可以改成这样(用开窗函数): select o.id, o.materialid, o.needAmount, i.totalamount-sum(o.needAmount) over(partition by o.materialid order by o.id)+o.needamount from t_storeitem i, t_orderticket o where i.materialid = o.materialid order by o.materialid
insert into t_storeitem values (1,1001,10000);
insert into t_storeitem values (2,1002,20000);
insert into t_storeitem values (3,1003,30000);create table t_orderticket (id number(5),materialid number(4),needAmount number(10));
insert into t_orderticket values (1,1001,100);
insert into t_orderticket values (2,1001,250);
insert into t_orderticket values (3,1001,150);
insert into t_orderticket values (4,1002,200);
insert into t_orderticket values (5,1002,100);
insert into t_orderticket values (6,1003,300);
commit;select t1.id,t1.materialid,t1.needAmount,
sum(decode(rn,1,t2.totalAmount,-t1.needAmount)) over(partition by t1.materialid order by t1.id) c_Amount
from (select id,materialid,needAmount,
row_number() over(partition by materialid order by id) rn
from t_orderticket) t1,t_storeitem t2
where t1.materialid=t2.materialid
id materialid needAmount c_Amount
-------------------------------------------
1 1 1001 100 10000
2 2 1001 250 9750
3 3 1001 150 9600
4 4 1002 200 20000
5 5 1002 100 19900
6 6 1003 300 30000
如果是同materialid的订单,它的remainAmount是库存数与 在它之前出现过的同materialid订单的needAmount的和 之差,即我要的结果是:orderticketid materialid remainAmount
1 1001 10000 (10000-0)
2 1001 9900 (10000-100)
3 1001 9650 (10000-100-250)
4 1002 20000 (20000-0)
5 1002 19800 (20000-200)
6 1003 30000 (30000-0) 不知你看明白了没有。?
select t1.id,t1.materialid,t1.needAmount,
lag(totalamount-needAmount,1,totalamount) over(partition by t1.materialid order by t1.id) c_Amount
from t_orderticket t1,t_storeitem t2
where t1.materialid=t2.materialid
id materialid needAmount c_Amount
-------------------------------------------
1 1 1001 100 10000
2 2 1001 250 9900
3 3 1001 150 9750
4 4 1002 200 20000
5 5 1002 100 19800
6 6 1003 300 30000
create table t_storeitem as
select 1 id,1001 materialid,10000 totalAmount from dual
union select 2,1002,20000 from dual
union select 3,1003,30000 from dual;create table t_orderticket as
select 1 id,1001 materialid,100 needAmount from dual
union select 2,1001,250 from dual
union select 3,1001,150 from dual
union select 3,1002,200 from dual
union select 3,1002,150 from dual
union select 3,1003,300 from dual;select rownum as orderticketid,a.materialid,lag(a.totalAmount-b.needAmount,1,a.totalAmount) over(partition by a.materialid order by a.id) as remainAmount from t_storeitem a,t_orderticket b where a.materialid=b.materialid
insert into t_storeitem values (1,1001,10000);
insert into t_storeitem values (2,1002,20000);
insert into t_storeitem values (3,1003,30000);create table t_orderticket (id number(5),materialid number(4),needAmount number(10));
insert into t_orderticket values (1,1001,100);
insert into t_orderticket values (2,1001,250);
insert into t_orderticket values (3,1001,150);
insert into t_orderticket values (4,1002,200);
insert into t_orderticket values (5,1002,100);
insert into t_orderticket values (6,1003,300);
insert into t_orderticket values (7,1003,600);
insert into t_orderticket values (8,1001,1000);
insert into t_orderticket values (9,1001,800);
commit;select t1.id,t1.materialid,t1.needAmount,
sum(decode(rn,1,t2.totalAmount,-(select needAmount from
(select needAmount,materialid,id from t_orderticket order by id desc) tt
where tt.materialid=t1.materialid and tt.id<t1.id and rownum<2))) over(partition by t1.materialid order by t1.id) c_Amount
from (select id,materialid,needAmount,
row_number() over(partition by materialid order by id) rn
from t_orderticket) t1,t_storeitem t2
where t1.materialid=t2.materialid id materialid needAmount c_Amount
-------------------------------------------
1 1 1001 100 10000
2 2 1001 250 9900
3 3 1001 150 9650
4 8 1001 1000 9500
5 9 1001 800 8500
6 4 1002 200 20000
7 5 1002 100 19800
8 6 1003 300 30000
9 7 1003 600 29700
nvl(totalamount-(select sum(needAmount) from t_orderticket a where a.materialid=t1.materialid and a.id<t1.id),totalamount) c
from t_orderticket t1,t_storeitem t2
where t1.materialid=t2.materialid
order by t1.materialid,t1.id
也可以改成这样(用开窗函数): select o.id,
o.materialid,
o.needAmount,
i.totalamount-sum(o.needAmount) over(partition by o.materialid order by o.id)+o.needamount
from t_storeitem i, t_orderticket o
where i.materialid = o.materialid
order by o.materialid