根据前一行的值更新SQL行
根据前一行的值更新SQL行
提问于 2013-04-03 14:16:08
我想要更新行值，其中有一些部分从以前的行值。(基本上我需要找到结转效果)。
我使用While loop或cursor来做同样的事情，但是它占用了很多时间，因为update语句运行了很多次。
请给我一个好的有效的方法来发现结转效果。下面是具有0.2进位效果的dataset的示例。
WeekNumber Var1 carry_over_effect_0.2
1 10 10
2 30 30+0.2*10
3 80 80+(30+0.2*10)*0.2诸若此类
我想再加一个，如下面的例子所示。
Brand WeekNumber Var1 carry_over_effect_0.2
x 1 10 10
x 2 30 30+0.2*10
x 3 80 80+(30+0.2*10)*0.2
y 1 40 40
y 2 50 50 + (40*0.2)以此类推。可以有一个或多个关键变量，如这里的Brand，它将整个集合划分为两个或多个子集。
回答 2
Stack Overflow用户
回答已采纳
发布于 2013-04-03 14:46:42
我假设排序是由WeekNumber定义的，并且这是连续的，没有间隙。与其进行更新，为什么不在select过程中执行计算(这样，您就不必担心计算会过期)：
declare @t table (WeekNumber int, Var1 int)
insert into @t (WeekNumber, Var1) values
(1, 10),
(2, 30),
(3, 80)
;with CarryOvers as (
select WeekNumber,Var1,CONVERT(decimal(38,4),Var1) as CarryOver from @t where WeekNumber=1
union all
select t.WeekNumber,t.Var1,CONVERT(decimal(38,4),t.Var1 + (0.2*co.CarryOver))
from @t t inner join CarryOvers co on t.WeekNumber = co.WeekNumber+1
)
select * from CarryOvers option (maxrecursion 0)结果：
WeekNumber Var1 CarryOver
----------- ----------- ---------------------------------------
1 10 10.0000
2 30 32.0000
3 80 86.4000UPDATE版本：
declare @t table (WeekNumber int, Var1 int,CarryOver decimal(38,4))
insert into @t (WeekNumber, Var1) values
(1, 10),
(2, 30),
(3, 80)
;with CarryOvers as (
select WeekNumber,Var1,CONVERT(decimal(38,4),Var1) as CarryOver from @t where WeekNumber=1
union all
select t.WeekNumber,t.Var1,CONVERT(decimal(38,4),t.Var1 + (0.2*co.CarryOver))
from @t t inner join CarryOvers co on t.WeekNumber = co.WeekNumber+1
)
update t set CarryOver = co.CarryOver
from @t t inner join CarryOvers co on t.WeekNumber = co.WeekNumber
option (maxrecursion 0)
select * from @t但我要重复一遍，我建议不要这样做，除非在正常使用期间运行SELECT是一个已证实的性能问题-表中的任何其他UPDATE现在都需要在之后立即运行此UPDATE，因此您会为每个UPDATE增加相当大的性能成本。
添加Brand后：
declare @t table (Brand char(1),WeekNumber int, Var1 int)
insert into @t (Brand,WeekNumber, Var1) values
('x',1, 10),
('x',2, 30),
('x',3, 80),
('y',1,40),
('y',2,50)
;with CarryOvers as (
select Brand,WeekNumber,Var1,CONVERT(decimal(38,4),Var1) as CarryOver from @t where WeekNumber=1
union all
select t.Brand,t.WeekNumber,t.Var1,CONVERT(decimal(38,4),t.Var1 + (0.2*co.CarryOver))
from @t t inner join CarryOvers co on t.WeekNumber = co.WeekNumber+1 and t.Brand = co.Brand
)
select * from CarryOvers
order by Brand,WeekNumber option (maxrecursion 0)Stack Overflow用户
发布于 2013-04-03 14:25:44
你的问题不清楚。但是可以考虑使用触发器
我不确定这样做是否有效。
CREATE TRIGGER NAME
BEFORE INSERT INTO TABLE_NAME
FOR EACH ROW BEGIN
WHERE (SELECT COUNT(*) FROM TABLE_nAME) > 0
BEGIN
INSERT INTO TABLE-NAME
SELECT NEW.WEEKNUMBER, NEW.VAR1, (NEW.VAR1+S.CARRY_OVER_EFFECT *0.2) AS CARRY_OVER_EFFECT
FROM
TABLE-NAME S
WHERE S.WEEKNUMBER + 1= NEW.WEEK_NUMBER;
END页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/15780154
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号