--是不是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 weekexec up_weekalter 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
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 weekexec up_weekalter 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
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
gocreate 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
goselect * 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
GOCREATE 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。不足的补足100.
但是上面的fetch结果好象不对
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 ,2insert 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 tselect * 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
但是把-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
@va int
asdeclare @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,2declare @aa int,@bb int,@cc int,@s intset @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 curselect * from @a order by [week],item
gotest 100