[SQL Server 2005/2008]递归更新update(使用CTE公用表达式)

 

-- 应用举例, t_user_sells中有每个用户每天的销售量, 现在需要得到每个用户每天的销量增长率。即：(当天销售量-昨日销售量)/昨日售销量* 100%

with Updaterecursive(startdate, userid, dealmoney, risepercent, rownumber)

as

(

   select startdate, userid, dealmoney, risepercent,

          row_number() over(partition by userid order by startdate asc) as rownumber

     from t_user_sells

)

 

 

update t set t.risepercent = (t. dealmoney - s. dealmoney) * 100.00 / s.dealmoney

  from Updaterecursive t left join Updaterecursive s

    on (t.userid = s.userid and t.rownumber = s.rownumber + 1)

 where t.rownumber > 1;

 

 

(CTE相对于普通SQL语句来说还是很耗内存的，测试了一下: 在奔4台式机上更新40万行记录，只需10秒)