SQL Server 使用row_num()实现递增更新

最近帮同事弄一功能：平均百分比。譬如说做某件事有5步，平均下来也就是：20%,40%,60%,80%,100%。

 

要是用代码来写的话比较容易：先获取总共有多少步，然后取个平均数，再按照排列顺序查询数据并循环数据，添加相应的百分比，最后设置最后一步为100%。

 

这样子写起来是不难，但我这个人比较懒，不想用代码写想用一个SQL直接搞定。再加上这个功能第一版时就是用一个SQL来实现的，这个第二版我不想处理的太复杂。

我的处理思路如下：

 

1、获取平均值，这个比较简单，一个avg就能搞定啦：

declare @avg int;
select @avg = (100/count(S_PRO_ID))  from S_ProgressDESC where S_PRO_ID=5;
print @avg;

 

2、将每个步骤排序，这个也不难，可以用SQL Server里面的row_num()方法：

select row_number() over (order by a.S_showorder) as per, a.S_ID  as s_id
from S_ProgressDESC a 
where a.S_PRO_ID=5

 

 PS：可以参考：http://blog.csdn.net/dxnn520/article/details/8093628，http://blog.csdn.net/star8816/article/details/3033888

 

3、将每个步骤按照排序乘以平均数，这个有点难。需要将以上两步联合起来并更新表。试了各种常用的如两个表联合更新，不常用的如将排序后的数据弄成需要更新表的一个字段再更新，都失败了。最后想到用子查询，自己写了几个看似没有问题，却无法执行的SQL。最后在百度上搜到一个有启发的方法，链接如下：http://576017120.iteye.com/blog/1947154（也不知道他这个是怎么执行对的，反正在我的机器上就老报错）。经启发并修改之后的SQL如下：

--1、获取平均值
declare @avg int;
select @avg = (100/count(S_PRO_ID))  from S_ProgressDESC 
where S_PRO_ID=5;
print @avg;

--2、排序并更新
update S_ProgressDESC 
set S_PRO_NUM = @avg * aa.per from (
select row_number() over (order by a.S_showorder) as per, a.S_ID  as s_id 
from S_ProgressDESC a where a.S_PRO_ID=5 
) as aa
where S_PRO_ID=5 and S_ProgressDESC.S_ID=aa.S_ID

 

4、将最后一步的百分比设置为100%：

update S_percent='100%' from S_ProgressDESC 
where S_showorder=(
select max(S_showorder) from S_ProgressDESC where S_PRO_ID=5) 
where S_PRO_ID=5;

  

PS：测试环境：SQL Server 2005