如何按照条件插入数据，使得累计值大于一定值？

[intellectual1]

原表：
week qty Item
1 100 1
1 50 1
1 -300 2
2 -500 2
2 -100 2
2 -300 2
2 150 1

分析：
week qty Item
1 100 1
1 50 1
1 -300 2

week 1 累计值 -150
想要保持week 1累计值为 100
插入一条纪录
week qty Item
1 250 3

插入此条纪录后
week 1 和 week 2的累计值为 -650

week qty Item
1 100 1
1 50 1
1 -300 2
1 250 3
2 -500 2
2 -100 2
2 -300 2
2 150 1

在week2中插入一条纪录
使得week 1 和 week 2的累计值也为100
week qty Item
2 750 3

最终插入两条纪录的结果表：
week qty Item
1 100 1
1 50 1
1 -300 2
1 250 3
2 -500 2
2 -100 2
2 -300 2
2 150 1
2 750 3
请问如何建立存储过程，自动插入Item 3的那两条纪录？

[CathySun118]

up

[gahade]

--是不是week如果有1,2,3,4,5的话,要先把1,2,3,4平衡为100,5再按总的平衡为100呢?

drop table weektest
go
create table weektest(week int,qty int,item int)
go
insert into weektest
select 1,100,1
union all select 1,50,1
union all select 1,-300,2
union all select 2,-500,2
union all select 2,-100,2
union all select 2,-300,2
union all select 2,150,1
go
select * from weektest order by week

exec up_week

alter proc up_week
as
declare @maxweek int
select @maxweek = max(week) from weektest
declare @week int
declare @qty int
declare @item int
declare cur_tmp cursor for
select distinct week from weektest
open cur_tmp
fetch next from cur_tmp into @week
while @@fetch_status=0
begin
if @week <> @maxweek
begin
select @qty = sum(qty) from weektest where week=@week
if @qty<>100
begin
select @item=max(item)+1 from weektest where week=@week
insert into weektest(week,qty,item)
select @week,100 - @qty,@item
end
end
else
begin
select @qty = sum(qty) from weektest
if @qty<>100
begin
select @item=max(item)+1 from weektest where week=@maxweek
insert into weektest(week,qty,item)
select @week,100 - @qty,@item
end
end
fetch next from cur_tmp into @week
end
close cur_tmp
deallocate cur_tmp

[gahade]

drop table weektest
go
create table weektest(week int,qty int,item int)
go
insert into weektest
select 1,100,1
union all select 1,50,1
union all select 1,-300,2
union all select 2,-500,2
union all select 2,-100,2
union all select 2,-300,2
union all select 2,150,1
go

create proc up_week
as
declare @maxweek int
select @maxweek = max(week) from weektest
declare @week int
declare @qty int
declare @item int
declare cur_tmp cursor for
select distinct week from weektest
open cur_tmp
fetch next from cur_tmp into @week
while @@fetch_status=0
begin
if @week <> @maxweek
begin
select @qty = sum(qty) from weektest where week=@week
if @qty<>100
begin
select @item=max(item)+1 from weektest where week=@week
insert into weektest(week,qty,item)
select @week,100 - @qty,@item
end
end
else
begin
select @qty = sum(qty) from weektest
if @qty<>100
begin
select @item=max(item)+1 from weektest where week=@maxweek
insert into weektest(week,qty,item)
select @week,100 - @qty,@item
end
end
fetch next from cur_tmp into @week
end
close cur_tmp
deallocate cur_tmp
go

select * from weektest order by week

/*
week qty item
----------- ----------- -----------
1 100 1
1 50 1
1 -300 2
1 250 3
2 150 1
2 -100 2
2 -300 2
2 -500 2
2 750 3

（所影响的行数为 9 行）
*/

[taochunsong]

看不懂，干嘛要这样的需求呢？

[新鲜鱼排]

主要是通过自定义函数。
create table t(week int, qty int, Item int)
insert t select 1, 100, 1
union all select 1, 50, 1
union all select 1, -300, 2
union all select 2, -500, 2
union all select 2, -100, 2
union all select 2, -300, 2
union all select 2, 150, 1
GO

CREATE function f_table(@week int)
returns @t table(week int, qty int, item int)
as
begin
declare @qty int
select @qty = sum(qty) from t group by week having week = @week
if @qty < 100
begin
insert into @t select @week, 100 - @qty, 3
end
return
end
Go

自己写动态语句来循环每一周。
insert into t
select dbo.f_table(1)

drop table t
drop function f_table

[intellectual1]

是的，按周排序，分组。
对于每一周(目标周)来说，从第一周到目标周的累计值都是100。不足的补足100.
但是上面的fetch结果好象不对

[playwarcraft]

--不曉得有沒有理解錯...好像邏輯挺簡單,也就第一周補100,以後的只要補當周就可以了吧
create table T([week] int,qty int,item int)
insert into T
select 1,100,1 union all
select 1,50,1 union all
select 1,-300,2 union all
select 2,-500,2 union all
select 2,-100,2 union all
select 2,-300,2 union all
select 2,150,1 union all
select 3,-30 ,2

insert into T([week],qty,item)
select [week],
[qty]=case when exists (select 1 from T a where a.[week]<T.[week])
then - sum(qty)
else 100-sum(qty) end,
item=3
from T
group by [week]

drop table t

select * from T
week qty item
----------- ----------- -----------
1 100 1
1 50 1
1 -300 2
2 -500 2
2 -100 2
2 -300 2
2 150 1
3 -30 2
1 250 3
2 750 3
3 30 3

[intellectual1]

第二次的fetch结果正确，
但是把-300改成-600，结果不对了
week qty Item
1 100 1
1 50 1
1 -600 2
2 -500 2
2 -100 2
2 -600 2
2 150 1

[playwarcraft]

如果把100,當成是0就更好理解了,每週各自補自己的.(換成100,也就第一周+100,以後的不用加)

[intellectual1]

第二次fetch结果正确

[chuifengde]

Create proc test
@va int
as

declare @b table([week] int,qty int,Item int)
declare @a table([week] int, qty int, Item int)
insert @a select 1, 100, 1
union all select 1 ,50, 1
union all select 1 ,-300 ,2
union all select 2 ,-500 ,2
union all select 2 ,-100 ,2
union all select 2 ,-300, 2
union all select 2 ,150, 1
union all select 3,-200,2
union all select 3,200,2

declare @aa int,@bb int,@cc int,@s int

set @s=0
declare cur cursor for
select [week],sum(qty) q1,max(item) from @a group by [week] order by [week]
open cur
fetch next from cur into @aa,@bb,@cc
while @@fetch_status=0
begin
insert @a select @aa,@va-@bb-case @s when 0 then 0 else 100 end, @cc+1
fetch next from cur into @aa,@bb,@cc
set @s=@s+1
end
close cur
deallocate cur

select * from @a order by [week],item
go

test 100

[intellectual1]

select into 结果也对

[intellectual1]

还有游标的方法，都齐了