使用左连接 select applyno,rcin.storeid,applyweight,iden,stockweight from rcin left join applydetail on applydetail.storeid=rcin.storeid
wsg269662(虾哥) : 这位大侠,看清楚我给的条件及结果。哪有这么简单
---------------------------- --搭建环境: create table ApplyDetail(Apply_No char(10), Store_ID char(10), Apply_Weight int) insert into applydetail select 'AAA', 'Int1', 100 union select 'AAA', 'Int2', 200 union select 'AAA', 'Int3', 500create table rcin(iden char(5),store_id char(4),stock_weight int) insert into rcin select 'Iden1', 'Int1', 60 union select 'Iden2', 'Int1', 120 union select 'Iden3', 'Int2', 500 union select 'Iden4', 'Int3', 300 union select 'Iden5', 'Int3', 100 union select 'Iden6', 'Int3', 450-----------------------------环境OKselect *,n=0 into t from rcin --建一个临时表declare @value int declare @flag char(10)--------------得到每类Int每条记录的累加值 update t set @value = case when store_id = @flag then @value + stock_weight else stock_weight end, n = @value, @flag = store_id--------------得到累加值小于等于所需物料总量的所有记录 select iden,stock_weight from t,applydetail b where t.store_id = b.Store_ID and t.n <= b.apply_weightunion all--------------得到累加值大于所需物料总量,且累加值最小的那条记录,将物--------------料字段改为(该记录物料值-(累加值-所需物料总值)) select a.iden,b.stock_weight-a.pass from ( select iden=min(iden),pass=min(n)-b.apply_weight from t,ApplyDetail b where t.store_id = b.Store_ID and t.n > b.apply_weight group by b.store_id,b.apply_weight) as a, t b where a.iden = b.iden 结果: Iden1 60 Iden4 300 Iden5 100 Iden2 40 Iden3 200 Iden6 100
出去溜了一圈,突然想出一个好办法: declare @flag char(10) declare @value intupdate rcin set @value = case when rcin.store_id=@flag then @value-stock_weight else b.apply_weight - stock_weight end, stock_weight = case when @value > 0 then stock_weight else @value+stock_weight end, @flag = b.store_id from applydetail b where rcin.store_id =b.store_id结果rcin表中stock_weight大于零的记录便是所需要的结果集.
select applyno,rcin.storeid,applyweight,iden,stockweight from rcin left join applydetail on
applydetail.storeid=rcin.storeid
这位大侠,看清楚我给的条件及结果。哪有这么简单
--搭建环境:
create table ApplyDetail(Apply_No char(10), Store_ID char(10), Apply_Weight int)
insert into applydetail
select 'AAA', 'Int1', 100 union
select 'AAA', 'Int2', 200 union
select 'AAA', 'Int3', 500create table rcin(iden char(5),store_id char(4),stock_weight int)
insert into rcin
select 'Iden1', 'Int1', 60 union select
'Iden2', 'Int1', 120 union select
'Iden3', 'Int2', 500 union select
'Iden4', 'Int3', 300 union select
'Iden5', 'Int3', 100 union select
'Iden6', 'Int3', 450-----------------------------环境OKselect *,n=0 into t from rcin --建一个临时表declare @value int
declare @flag char(10)--------------得到每类Int每条记录的累加值
update t
set @value = case when store_id = @flag then @value + stock_weight else stock_weight end,
n = @value,
@flag = store_id--------------得到累加值小于等于所需物料总量的所有记录
select iden,stock_weight
from t,applydetail b
where t.store_id = b.Store_ID
and t.n <= b.apply_weightunion all--------------得到累加值大于所需物料总量,且累加值最小的那条记录,将物--------------料字段改为(该记录物料值-(累加值-所需物料总值))
select a.iden,b.stock_weight-a.pass
from
(
select iden=min(iden),pass=min(n)-b.apply_weight
from t,ApplyDetail b
where t.store_id = b.Store_ID
and t.n > b.apply_weight
group by b.store_id,b.apply_weight)
as a,
t b
where a.iden = b.iden 结果:
Iden1 60
Iden4 300
Iden5 100
Iden2 40
Iden3 200
Iden6 100
declare @flag char(10)
declare @value intupdate rcin
set @value = case when rcin.store_id=@flag then @value-stock_weight
else b.apply_weight - stock_weight end,
stock_weight = case when @value > 0 then stock_weight
else @value+stock_weight end,
@flag = b.store_id
from applydetail b
where rcin.store_id =b.store_id结果rcin表中stock_weight大于零的记录便是所需要的结果集.
不过要求RcIn表的Store_ID字段是连续的.这样update才会按顺序来.