根据一列更新另一列的问题
原表（week4没有数据）
IDweekitemqtyqty2
11250
22250
33250
451100
561100
671100
781100
现在需要
qty 2 的数据更新
Item 为2的往下顺延3个week
itemqtyqty2
250
250
250
50
110050
110050
Item 为1的往下顺延1个week
1100
1100100
1100100
1100100
最终结果：
如何用存储过程实现？
IDweekitemqtyqty2
11250
22250
33250
8450
45110050
561100150
671100100
781100100
[解决办法]
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
