SQL Server：如何将随机整数插入表中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6371490/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server: how to insert random integers into table?
提问by jrara
I have a test table like this
我有一个这样的测试表
CREATE TABLE #temp (
rid INT IDENTITY (1, 1) NOT NULL,
val INT NULL,
CONSTRAINT pk_temp_rid PRIMARY KEY (rid)
);
What are the different methods in SQL Server to insert random integers into this table (e.g for 1000 rows). While loop, cross join or what?
SQL Server 中有哪些不同的方法可以将随机整数插入到该表中（例如，对于 1000 行）。While 循环，交叉连接还是什么？
I tried this but the result is not correct
我试过了，但结果不正确
DECLARE @val AS INT = 1;
WHILE @val <= 1000
BEGIN
INSERT #temp (val)
SELECT RAND(@val);
SET @val = @val + 1;
END
SELECT *
FROM #temp;
采纳答案by Tamer
you can use select CAST(RAND() * 1000000 AS INT) AS [RandomNumber]for generating or selecting random integers .
so the full query will be something like that :
您可以select CAST(RAND() * 1000000 AS INT) AS [RandomNumber]用于生成或选择随机整数。所以完整的查询将是这样的：
DECLARE @t TABLE( randnum float )
DECLARE @cnt INT; SET @cnt = 0
WHILE @cnt <=10000
BEGIN
SET @cnt = @cnt + 1
INSERT INTO @t
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
END
SELECT randnum, COUNT(*)
FROM @t
GROUP BY randnum
regards..
问候..
回答by SPE109
This also done above
这也做了上面
Insert Into @t
Select Cast(rand(checksum(newid()))*1000000 as int)
go 1000
See this link : https://www.mssqltips.com/sqlservertip/1467/populate-a-sql-server-column-with-a-sequential-number-not-using-an-identity/
请参阅此链接：https: //www.mssqltips.com/sqlservertip/1467/populate-a-sql-server-column-with-a-sequential-number-not-using-an-identity/
回答by Yuck
From SQL SERVER – Random Number Generator Script:
SELECT randomNumber, COUNT(1) countOfRandomNumber
FROM (SELECT ABS(CAST(NEWID() AS binary(6)) % 1000) + 1 randomNumber
FROM sysobjects) sample
GROUP BY randomNumber;
EDIT: Just to clarify, the script is grouping on the random number that was generated per row. So the total number of results is not guaranteed. You can be sure you'll never get more rows than SELECT COUNT(*) FROM sysobjects, though.
编辑：只是为了澄清，该脚本对每行生成的随机数进行分组。所以不能保证结果的总数。不过，您可以确定您永远不会得到比 多的行SELECT COUNT(*) FROM sysobjects。
回答by NullRef
I think a `while is going to work. You are very close.
我认为一段时间会起作用。你很亲近。
DECLARE @val AS INT = 1;
WHILE @val <= 1000
BEGIN
INSERT #temp (val)
SELECT cast((RAND()*1000) as int);
SET @val = @val + 1;
END
SELECT *
FROM ? #temp;
