数据库给字段添加随机数

目录

• 一.当数据表有唯一自增ID
• 二.数据表没有自增ID

 

---

回到顶部

一.当数据表有唯一自增ID

Declare  @CurentRecord int,@BeginNum int, @EndNum int, @TotalCount int,@Value int
set @BeginNum=1        -- 起止时间设定随机数范围,我想要1~3的随机数,所以起始数为1
set @EndNum=4          -- 终止数为4,不包含4
set @TotalCount=5      -- 生成随机数的个数
set @CurentRecord=1   -- 标志位
While @CurentRecord<=@TotalCount  
 Begin 
  set @Value= @BeginNum+(@EndNum - @BeginNum)*rand()
  -- select @Value
  update [testTable] set [level] = @Value where [ID] = @CurentRecord
  set @CurentRecord=@CurentRecord+1
 End

回到顶部

二.数据表没有自增ID

　　如果操作的数据表没有唯一自增ID时,在上边代码update中where [ID] = @CurentRecord显然是行不通的,在sql server中,我们可以通过row_number()over()来给表生成一个隐式编号,然后通过这个编号去操作:

  DECLARE @startnum int,@endnum int,@count int,@flag int, @value int
  set @startnum = 1  -- 生成1~5的随机数
  set @endnum = 6
  set @count = 1000
  set @flag = 1
  while @flag <= @count
    Begin
        Set @value = @startnum + (@endnum - @startnum)*RAND()
        update [t1] set t1.level = @value from t1, (SELECT top 1000 ROW_NUMBER()over(order by authorname) num,[packageDate],[authorName] FROM [t1]) B 
　　　　　　where t1.packageDate = B.packageDate and t1.authorName = B.authorName and B.num = @flag
        Set @flag = @flag + 1
    End