ROW_NUMBER()分析函数生成一个排序列

   
--ROW_NUMBER()根据排序字段重新赋值本身的排序重新刷新      
ALTER  proc UpdateTransportorder_proc      
 @OldVehicleStateType_ID int --修改之前的车辆类型       
as      
declare UpdateTransportorder_cursor cursor scroll for  
    
select EMTinCar.ID, ROW_NUMBER() over(order by EMTinCar.VehicleOrder_ID
 asc) as Rowno FROM EMTinCar where VehicleStateType_ID=@OldVehicleStateType_ID 
 ORDER BY EMTinCar.VehicleOrder_ID
open UpdateTransportorder_cursor   
declare @ID INT ,@Rowno INT,@VehicleOrder_ID INT      
FETCH first from UpdateTransportorder_cursor into @ID,@Rowno      
    
WHILE @@fetch_status=0                   
begin       
Update EMTinCar Set VehicleOrder_ID=@Rowno Where ID=@ID    --修改行        
fetch next from UpdateTransportorder_cursor into @ID,@Rowno    --移动游标字段必须跟查询的结果集一致       
end     

 
CLOSE UpdateTransportorder_cursor      
    
DEALLOCATE UpdateTransportorder_cursor