SQL SELECT语句生成随机数时出现重复值问题

问题根源 #

SQL Server的RAND()函数在单个查询执行周期内只会生成一次随机值，所有数据行都会复用这个固定值，这就是你看到governmentID、address数字前缀、phonenumber全表重复的核心原因。而NEWID()是每行独立生成新的GUID，所以姓名类字段没有重复问题。

修正后的SQL语句 #

将所有使用RAND()的地方，替换为RAND(CHECKSUM(NEWID()))生成每行独立的随机种子，确保每条记录的随机值唯一：

SELECT DISTINCT cp.CMN_PersonsID,
       SUBSTRING(REPLACE(CONVERT(varchar(100), NEWID()), '-', ''), 1, 6) AS firstname,
       SUBSTRING(REPLACE(CONVERT(varchar(100), NEWID()), '-', ''), 1, 2) AS middlename,
       SUBSTRING(REPLACE(CONVERT(varchar(100), NEWID()), '-', ''), 1, 12) AS lastname,
       -- 基于NEWID生成唯一种子，生成每行独立的9位数字
       CONVERT(numeric(9,0), FLOOR(RAND(CHECKSUM(NEWID())) * 999999999)) AS governmentID,
       -- 地址前缀改用带独立种子的随机数
       CONCAT(CAST(FLOOR(RAND(CHECKSUM(NEWID())) * 10000) AS INT), ' ', 
              SUBSTRING(REPLACE(CONVERT(varchar(100), NEWID()), '-', ''), 1, 6), ' Road') AS address, 
       DATEADD(DAY, RAND(CHECKSUM(NEWID()))*(1+DATEDIFF(DAY, '01/01/1945', DATEADD(yyyy, -17,GETDATE()))),'01/01/1945') AS DateofBirth,
       -- 简化生成10位唯一电话号码
       CAST(FLOOR(RAND(CHECKSUM(NEWID())) * 9000000000) + 1000000000 AS BIGINT) AS phonenumber,
       pfmap.DBName,
       pfmap.stu_award_year_token
INTO #person
FROM dbo.CMN_Persons AS cp
JOIN JHU_PF_StudentMapping pfmap ON pfmap.CMN_PersonsID = cp.CMN_PersonsID
WHERE pfmap.award_year_token IN (2021)
AND cp.CMN_PersonsID IN (2963806,2959027,2972326,2971359)

关键调整说明 #

• governmentID：通过CHECKSUM(NEWID())为RAND()提供每行唯一的种子，避免全表复用同一随机数
• address前缀：替换原RAND()为带独立种子的版本，确保每条地址的数字前缀不同
• phonenumber：简化生成逻辑，直接生成10位有效数字（范围1000000000-9999999999），同时保证唯一性

执行修正后的SQL后，重复的字段将为每条记录生成独立的随机值，不会再出现全表一致的情况。

内容的提问来源于stack exchange，提问作者Christine