如何按照条件插入数据，使得累计值大于一定值？
原表：
weekqtyItem
11001
1501
1-3002
2-5002
2-1002
2-3002
21501
分析：
weekqtyItem
11001
1501
1-3002
week 1 累计值 -150
想要保持week 1累计值为 100
插入一条纪录
weekqtyItem
12503
插入此条纪录后
week 1 和 week 2的累计值为 -650
weekqtyItem
11001
1501
1-3002
12503
2-5002
2-1002
2-3002
21501
在week2中插入一条纪录
使得week 1 和 week 2的累计值也为100
weekqtyItem
27503
最终插入两条纪录的结果表：
weekqtyItem
11001
1501
1-3002
12503
2-5002
2-1002
2-3002
21501
27503
请问如何建立存储过程，自动插入Item 3的那两条纪录？
[解决办法]
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 行）
*/
[解决办法]
主要是通过自定义函数。
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
------解决方案--------------------
--不曉得有沒有理解錯...好像邏輯挺簡單,也就第一周補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
[解决办法]
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
