根据一列更新另一列的问题
原表（week4没有数据）
ID week item qty qty2
1 1 2 50
2 2 2 50
3 3 2 50
4 5 1 100
5 6 1 100
6 7 1 100
7 8 1 100

现在需要
qty   2   的数据更新  
Item   为2的往下顺延3个week

item qty qty2
2 50
2 50
2 50
50
1 100 50
1 100 50

Item   为1的往下顺延1个week

1 100
1 100 100
1 100 100
1 100 100


最终结果：

如何用存储过程实现？
ID week item qty qty2
1 1 2 50
2 2 2 50
3 3 2 50
8 4 50
4 5 1 100 50
5 6 1 100 150
6 7 1 100 100
7 8 1 100 100



------解决方案--------------------

CREATE TABLE [dbo].[a](
[ID] [int] NULL,
[week] [int] NULL,
[item] [int] NULL,
[qty] [int] NULL,
[qty2] [int] NULL
) ON [PRIMARY]

go

INSERT INTO dbo.a (ID,week,item,qty)
VALUES (1,1,2,50);
INSERT INTO dbo.a (ID,week,item,qty)
VALUES (2,2,2,50);
INSERT INTO dbo.a (ID,week,item,qty)
VALUES (3,3,2,50);
INSERT INTO dbo.a (ID,week,item,qty)
VALUES (4,5,1,100);
INSERT INTO dbo.a (ID,week,item,qty)
VALUES (5,6,1,100);
INSERT INTO dbo.a (ID,week,item,qty)
VALUES (6,7,1,100);
INSERT INTO dbo.a (ID,week,item,qty)
VALUES (7,8,1,100);
GO
INSERT INTO a (week)
SELECT week
FROM (SELECT week + 3 AS week FROM a WHERE (item = 2)
UNION
SELECT week + 1 AS week FROM a WHERE (item = 1)) a
WHERE (week NOT IN (SELECT week FROM a))
GO
SELECT b.ID, b.week, b.item, b.qty, a.qty2
FROM (SELECT week, SUM(qty2) AS qty2
FROM (SELECT week + 3 AS week, qty AS qty2
FROM a
WHERE (item = 2)
UNION ALL
SELECT week + 1 AS week, qty AS qty2
FROM a
WHERE (item = 1)) a
GROUP BY week) a RIGHT OUTER JOIN
a b ON a.week = b.week
DROP TABLE A
------解决方案--------------------
/*
create table week4(ID int,week int,item int,qty int,qty2 int)
insert into week4 values(1,1,2,50,null)
insert into week4 values(1,2,2,50,null)
insert into week4 values(1,3,2,50,null)
insert into week4 values(1,5,1,100,null)
insert into week4 values(1,6,1,100,null)
insert into week4 values(1,7,1,100,null)
insert into week4 values(1,8,1,100,null)

select *from week4
*/
update week4
set qty2=w.qty2
from(
select (case when item=2 then week+3 else week+1 end) week,sum(qty) qty2
from week4
group by (case when item=2 then week+3 else week+1 end)
)w
where w.week=week4.week

insert into week4 select 1,a.week,null,null,a.qty2--如果ID是自动编号，那么 "1 "可以去掉
from( select (case when item=2 then week+3 else week+1 end) week,sum(qty) qty2
from week4
group by (case when item=2 then week+3 else week+1 end)
)a
left join week4 w on a.week=w.week
where w.id is null