现有表Table :pro 表
pro batch orderqty proqty
pro1 01 1000 200
pro1 02 1000 300
pro1 03 1000 400
pro1 04 1000 100
pro2 01 1200 100
pro2 02 1200 300
pro2 03 1200 400
要得到如下表结果:prod 表
pro batch orderqty proqty levqty
pro1 01 1000 200 800
pro1 02 800 300 500
pro1 03 500 400 100
pro1 04 100 100 0
pro2 01 1200 100 1100
pro2 02 1100 300 800
pro2 03 800 400 400说明:
pro1表示产品,batch(01,02,03、、、表示批次,从小到大递增的),orderqty 表示订单数,proqty 表示生数,
levqty 表示欠产数(levqty=orderqty-proqty)
就是要把上一批次欠产数作为下一批次的订单数,依次类推!
pro batch orderqty proqty
pro1 01 1000 200
pro1 02 1000 300
pro1 03 1000 400
pro1 04 1000 100
pro2 01 1200 100
pro2 02 1200 300
pro2 03 1200 400
要得到如下表结果:prod 表
pro batch orderqty proqty levqty
pro1 01 1000 200 800
pro1 02 800 300 500
pro1 03 500 400 100
pro1 04 100 100 0
pro2 01 1200 100 1100
pro2 02 1100 300 800
pro2 03 800 400 400说明:
pro1表示产品,batch(01,02,03、、、表示批次,从小到大递增的),orderqty 表示订单数,proqty 表示生数,
levqty 表示欠产数(levqty=orderqty-proqty)
就是要把上一批次欠产数作为下一批次的订单数,依次类推!
create table pro(pro varchar(10),batch varchar(10),orderqty int,proqty int)
insert into pro
select 'pro1','01',1000,200 union all
select 'pro1','02',1000,300 union all
select 'pro1','03',1000,400 union all
select 'pro1','04',1000,100 union all
select 'pro2','01',1200,100 union all
select 'pro2','02',1200,300 union all
select 'pro2','03',1200,400
goselect *,qty=orderqty - (select sum(proqty) from pro where pro = t.pro and batch <= t.batch)
from pro tdrop table pro/************pro batch orderqty proqty qty
---------- ---------- ----------- ----------- -----------
pro1 01 1000 200 800
pro1 02 1000 300 500
pro1 03 1000 400 100
pro1 04 1000 100 0
pro2 01 1200 100 1100
pro2 02 1200 300 800
pro2 03 1200 400 400(7 行受影响)
select pro,batch,orderqty=proqty+qty,proqty,qty
from(
select pro,batch,orderqty,proqty,
qty=orderqty - (select sum(proqty) from pro where pro = t.pro and batch <= t.batch)
from pro t
)t/*************pro batch orderqty proqty qty
---------- ---------- ----------- ----------- -----------
pro1 01 1000 200 800
pro1 02 800 300 500
pro1 03 500 400 100
pro1 04 100 100 0
pro2 01 1200 100 1100
pro2 02 1100 300 800
pro2 03 800 400 400(7 行受影响)
create table #t (pro varchar(4),batch varchar(2),orderqty int,proqty int)
insert into #t
select 'pro1','01',1000,200 union all
select 'pro1','02',1000,300 union all
select 'pro1','03',1000,400 union all
select 'pro1','04',1000,100 union all
select 'pro2','01',1200,100 union all
select 'pro2','02',1200,300 union all
select 'pro2','03',1200,400with t as (
select pro,batch ,orderqty ,proqty ,orderqty -proqty levQty from #t where batch ='01'
union all
select a.pro ,a.batch ,b.levQty ,a.proqty ,b.levQty -a.proqty from #t a
join t b on cast(a.batch as int) =cast(b.batch as int)+1
and a.pro =b.pro
)select * from t order by propro batch orderqty proqty levQty
pro1 01 1000 200 800
pro1 02 800 300 500
pro1 03 500 400 100
pro1 04 100 100 0
pro2 01 1200 100 1100
pro2 02 1100 300 800
pro2 03 800 400 400
select pro,batch,
orderqty=proqty + orderqty - (select sum(proqty) from pro where pro = t.pro and batch <= t.batch),
proqty,
qty=orderqty - (select sum(proqty) from pro where pro = t.pro and batch <= t.batch)
from pro t/************pro batch orderqty proqty qty
---------- ---------- ----------- ----------- -----------
pro1 01 1000 200 800
pro1 02 800 300 500
pro1 03 500 400 100
pro1 04 100 100 0
pro2 01 1200 100 1100
pro2 02 1100 300 800
pro2 03 800 400 400(7 行受影响)