SQLServer实现修改表时将列赋值为随机数

作者：袖梨 2022-06-29

这里没有特殊要求或者限制。仅有的限制条件是：用纯SQL来实现，而且我不想使用任何游标，不想将表中记录逐行遍历，并随机分配值。
背景

待解决问题的环境和表名等等可能不一样，但是有相同的规律。
我从Vehicle表开始解决问题。该表和'Type'、'Colour'两个表有对应关系。Colour表也包括连接到另一个表的'Finish'字段（即粗糙或者光滑）。
我需要保证的是，所有指定类型的交通工具（比如小型轿车）的属性，都是从colour表中根据finish字段随机分配的颜色。
问题的关键点：1.如何创建一个与指定标准相匹配的数据集合（包括多条记录）；2.将上述数据集合指定到不同表（例如所有小型轿车）之内的记录上。
我的解决方案是:

生成一个临时表，要求该表包含一条含有唯一数字，从数据集中获取的随机指定值的记录。

临时指定一个唯一记录到目标表的每一条记录，用临时表的唯一行实现表连接。

用新连接的临时表中随机指定的值更新目标表。

我在文章中贴出了所有的必须的SQL脚本，包括创建实例数据库，填入一系列随机数据，在有问题的地方，运行SQL脚本即可。
生成数据库中各表的脚本

下列代码是创建数据库结构的SQL脚本:

Vehicle 表

代码如下	复制代码
CREATE TABLE [dbo].[Vehicle]( [VehicleID] [int] IDENTITY(1,1) NOT NULL, [Description] [nvarchar](max) NOT NULL, [TypeID] [int] NOT NULL, [ColourID] [int] NULL, CONSTRAINT [PK_Car] PRIMARY KEY CLUSTERED ( [VehicleID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

Type 表

代码如下	复制代码
CREATE TABLE [dbo].[Type]( [TypeID] [int] IDENTITY(1,1) NOT NULL, [Description] [nvarchar](max) NOT NULL, CONSTRAINT [PK_Type] PRIMARY KEY CLUSTERED ( [TypeID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

Colour 表

代码如下

复制代码

CREATE TABLE [dbo].[Colour](
    [ColourID] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
    [FinishID] [int] NOT NULL,
CONSTRAINT [PK_Colour] PRIMARY KEY CLUSTERED
(
    [ColourID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Colour] ADD CONSTRAINT [DF_Colour_FinishID] DEFAULT ((1)) FOR [FinishID]
GO

Finish 表

代码如下	复制代码
CREATE TABLE [dbo].[Finish]( [FinishID] [int] IDENTITY(1,1) NOT NULL, [Description] [nvarchar](max) NOT NULL, CONSTRAINT [PK_Finish] PRIMARY KEY CLUSTERED ( [FinishID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO

生成内容的脚本

Vehicle 表的数据

代码如下

复制代码

Insert Into Vehicle(Description, TypeID) Values('Ford Focus',2)
Insert Into Vehicle(Description, TypeID) Values('Mini',1)
Insert Into Vehicle(Description, TypeID) Values('Ford Transit',4)
Insert Into Vehicle(Description, TypeID) Values('Audi A6',3)
Insert Into Vehicle(Description, TypeID) Values('VW Golf',2)
Insert Into Vehicle(Description, TypeID) Values('Robin Reliant',2)
Insert Into Vehicle(Description, TypeID) Values('Land Rover',3)
Insert Into Vehicle(Description, TypeID) Values('VW Polo',1)
Insert Into Vehicle(Description, TypeID) Values('VW Passat',3)
Insert Into Vehicle(Description, TypeID) Values('Vauxhall Corsa',2)
Insert Into Vehicle(Description, TypeID) Values('Ford Ka',1)
Insert Into Vehicle(Description, TypeID) Values('Smart Car',1)

Type 表的数据

代码如下	复制代码
Insert Into Type(Description) Values('Small Car') Insert Into Type(Description) Values('Medium Car') Insert Into Type(Description) Values('Large Car') Insert Into Type(Description) Values('Truck')

Colour 表的数据

代码如下

复制代码

Insert Into Colour(Description, FinishID) Values('Red',1)
Insert Into Colour(Description, FinishID) Values('Blue',2)
Insert Into Colour(Description, FinishID) Values('Green',2)
Insert Into Colour(Description, FinishID) Values('Orange',1)
Insert Into Colour(Description, FinishID) Values('Yellow',2)
Insert Into Colour(Description, FinishID) Values('Silver',1)
Insert Into Colour(Description, FinishID) Values('Black',2)
Insert Into Colour(Description, FinishID) Values('White',2)
Insert Into Colour(Description, FinishID) Values('Purple',1)

Finish 表的数据

Insert Into Finish(Description) Values('Matte')
Insert Into Finish(Description) Values('Glossy')
用Colour表的主键随机更新Vehicle表的脚本

代码如下

复制代码

Declare @TempTable Table(
RowNumber Int,
ColourID Int)

Declare @VehicleTypeID As Int = 1 --Set this to whatever finish type is required

Declare @FinishID Int = 1
Declare @Count Int = 1
Declare @NumberOfVehicles Int = (Select count(*) from Vehicle where TypeID = @VehicleTypeID)

--This generates a temporary table with a unique row number and randomly assigned IDs
While (@Count <= @NumberOfVehicles)
Begin
Insert Into @TempTable values (@Count, (select top 1 ColourID
      from Colour where FinishID = @FinishID order by checksum(newid())))
Set @Count = @Count + 1
End

--This updates the Vehicle table according to specific criteria
--(Vehicle Type) and assigns the random IDs from the temporary table
Update
    TempVehicle
Set
    TempVehicle.ColourID = TempColour.ColourID
From
    (select
        row_number() over(order by VehicleID) as RowNumber,
        VehicleID,
        TypeID,
        ColourID
    from
        Vehicle
    where
        TypeID = @VehicleTypeID) as TempVehicle
Right Join
    @TempTable as TempColour on TempColour.RowNumber = TempVehicle.RowNumber
Where
    TempVehicle.RowNumber = TempColour.RowNumber
And
    TempVehicle.TypeID = @VehicleTypeID
And
    TempVehicle.ColourID Is Null
    --Included to ensure rows that have already been assigned
    --a random colour are not updated

总结

但愿我把问题描述清楚了。并且提供了一个能保持外键不变，且用随机指定的值来更新数据库表，的解决方案。
我不确定这类问题是否在CodeProject网站上发表过，但是我没有找到任何一个涉及到该问题的文章。如果有扩展的需求，请通知我，我将全力以赴来实现您的需求。