SQl给表字段批量更新指定长度的随机数，且每行数据都不一样

直接上SQL：

--固定长度为4位随机数,即1000~9999之间的随机数:
Select cast(rand(checksum(newid()))*(99999-10000)+10000 as decimal(18,2))

--批量更新表字段数据为随机数，且每个数据都不一样
update [BrandRebate] set 
[DailyRebate] = cast(rand(checksum(newid()))*(999-100)+100 as decimal(18,2)),
[MonthRebate]= cast(rand(checksum(newid()))*(9999-1000)+1000 as decimal(18,2)),
[SeasonRebate]= cast(rand(checksum(newid()))*(99999-10000)+10000 as decimal(18,2)),
[YearRebate]= cast(rand(checksum(newid()))*(999999-100000)+100000 as decimal(18,2)),
 [OtherRebate]= cast(rand(checksum(newid()))*(999-100)+100 as decimal(18,2)),
 [PricingShare]= cast(rand(checksum(newid()))*(999-100)+100 as decimal(18,2)),
 [PeriodicReward]= cast(rand(checksum(newid()))*(999-100)+100 as decimal(18,2)),
 [MoneyReward]= cast(rand(checksum(newid()))*(999-100)+100 as decimal(18,2)),
 [RetailReward]= cast(rand(checksum(newid()))*(999-100)+100 as decimal(18,2)),
 [PhoneActivatedReward]= cast(rand(checksum(newid()))*(999-100)+100 as decimal(18,2)),
 [EnReward]= cast(rand(checksum(newid()))*(999-100)+100 as decimal(18,2)),
 [PlatformRebate]= cast(rand(checksum(newid()))*(999-100)+100 as decimal(18,2)),
 [FillPost]= cast(rand(checksum(newid()))*(999-100)+100 as decimal(18,2)),
  [PromoteSales]= cast(rand(checksum(newid()))*(999-100)+100 as decimal(18,2)),
  [PriceConfirmed]= cast(rand(checksum(newid()))*(999-100)+100 as decimal(18,2))

效果如下：

 

 说明：

0~9，1位随机数：Select cast(rand(checksum(newid()))*(9-1)+1 as int)

10~99，2位随机数：Select cast(rand(checksum(newid()))*(99-10)+10 as int)

100~999，3位随机数：Select cast(rand(checksum(newid()))*(999-100)+100 as int)

1000~9999，4位随机数：Select cast(rand(checksum(newid()))*(9999-1000)+1000 as int) 

...以此类推...

整数用 int，如：Select cast(rand(checksum(newid()))*(999-100)+100 as int)

保留2位小数，如：Select cast(rand(checksum(newid()))*(999-100)+100 as decimal(18,2))

用来造数据 还挺方便的^_^