简单随机更新——newid()

newid()生成的是uniqueidentifier类型的唯一值，因此可以作为随机排序的参照字段

考虑以下场景：

有n个消费者分布在全国各地，由每地的会员店来维护，每个会员有若干个导购，假定有m个会员店，每个会员店的导购数量为a，a>=0，现在要将会员店的导购随机分配到每个消费者，如果该会员店没有导购，则暂不维护。

消费者与会员店一对一关系；会员店与导购一对多关系；

--会员关系表
create table tableA (rankid int,clientid int,retailer int,promotor int)
--会员店与导购关系表
create table tableB (retailer int,promotor int)

--while方法实现
declare @num int =1,@maxid int
select @maxid=MAX(rankid) from tableA 
while @num<=@maxid
begin
  declare @promotor int=0 ,@retailer int=0
  select @retailer =retailer from tableA where rankid=@num
  select top 1 @promotor = promotor from tableB b where b.Retailer=@retailer order by NEWID() 
  if @promotor is null  set @promotor =0
  
  update tableA set promotor= @promotor where rankid=@num
  
  set @num=@num+1
end

--游标方法实现
declare @clientid int
declare mycursor cursor for   
select clientid from tableA
open mycursor 
fetch next from mycursor into @clientid    
while (@@fetch_status=0)  
begin      
   
   declare @promotor1 int=0 ,@retailer1 int =0
   select @retailer1 =retailer from tableA where clientid=@clientid
   select top 1 @promotor1 = promotor from tableB b where b.Retailer=@retailer1 order by NEWID() 
   if @promotor1 is null  set @promotor1 =0
  
   update tableA set promotor= @promotor1 where clientid=@clientid
   fetch next from mycursor into @clientid
end  
close mycursor 
DEALLOCATE mycursor