sql-server - 使用一定范围内的随机数字更新表列

Question

我想更新表格列 -Code-，其中的数字在其范围内进行打乱，没有重复项，也不会丢失范围内的数字。例如范围是1-9，如下表:

Id|Name|Code

1 | AC | 2  
2 | AB | 1  
3 | CB | 5  
4 | DE | 9  
5 | FE | 3  
6 | AE | 4  
7 | FD | 6  
8 | BD | 7  
9 | DC | 8  

我想要像上面例子中那样的结果。我正在使用SQL Server 2008。

Answer 1

试试这个:

DECLARE @t TABLE ( ID INT, Code INT )

INSERT  INTO @t
        ( ID )
VALUES  ( 1 ),
        ( 2 ),
        ( 3 ),
        ( 4 ),
        ( 5 ),
        ( 6 ),
        ( 7 ),
        ( 8 ),
        ( 9 );
WITH    cte
          AS ( SELECT   * ,
                        ROW_NUMBER() OVER ( ORDER BY NEWID() ) AS rn
               FROM     @t
             )
    UPDATE  cte
    SET     code = rn


SELECT  *
FROM    @t

输出:

ID  Code
1   2
2   7
3   4
4   1
5   6
6   8
7   5
8   9
9   3

如果您想手动设置范围，则可以设置范围的起始编号并执行以下操作:

DECLARE @start INT = 101

DECLARE @t TABLE ( ID INT, Code INT )

INSERT  INTO @t
        ( ID )
VALUES  ( 1 ),
        ( 2 ),
        ( 3 ),
        ( 4 ),
        ( 5 ),
        ( 6 ),
        ( 7 ),
        ( 8 ),
        ( 9 );

WITH    cte
          AS ( SELECT   * ,
                        ROW_NUMBER() OVER ( ORDER BY NEWID() ) AS rn
               FROM     @t
             )
    UPDATE  cte
    SET     code = rn + @start - 1


SELECT  *
FROM    @t    

输出:

ID  Code
1   104
2   108
3   107
4   105
5   102
6   103
7   106
8   101
9   109