如何在 SQL Server 2005 中将随机数作为列返回？

How do I return random numbers as a column in SQL Server 2005?

提问by Joshua Carmody

I'm running a SQL query on SQL Server 2005, and in addition to 2 columns being queried from the database, I'd also like to return 1 column of random numbers along with them. I tried this:

我在 SQL Server 2005 上运行 SQL 查询，除了从数据库查询 2 列之外，我还想与它们一起返回 1 列随机数。我试过这个：

select column1, column2, floor(rand() * 10000) as column3 
from table1

Which kinda works, but the problem is that this query returns the same random number on every row. It's a different number each time you run the query, but it doesn't vary from row to row. How can I do this and get a new random number for each row?

哪种工作，但问题是这个查询在每一行返回相同的随机数。每次运行查询时它都是一个不同的数字，但它不会因行而异。我怎样才能做到这一点并为每一行获取一个新的随机数？

回答by Timothy Khouri

I realize this is an older post... but you don't need a view.

我意识到这是一个较旧的帖子......但你不需要视图。

select column1, column2, 
  ABS(CAST(CAST(NEWID() AS VARBINARY) AS int)) % 10000 as column3 
from table1

回答by Sam Saffron

WARNING

警告

Adam's answerinvolving the view is very inefficient and for very large sets can take out your database for quite a while, I would strongly recommend against using it on a regular basis or in situations where you need to populate large tables in production.

Adam涉及视图的答案非常低效，并且对于非常大的集合可能会占用您的数据库很长一段时间，我强烈建议不要定期使用它，或者在需要在生产中填充大型表的情况下使用它。

Instead you could use this answer.

相反，您可以使用此答案。

Proof:

证明：

CREATE VIEW vRandNumber
AS
SELECT RAND() as RandNumber

go 

CREATE FUNCTION RandNumber()
RETURNS float
AS
  BEGIN
  RETURN (SELECT RandNumber FROM vRandNumber)
  END

go 

create table bigtable(i int)

go 

insert into bigtable 
select top 100000 1 from sysobjects  a
join sysobjects b on 1=1

go 

select cast(dbo.RandNumber() * 10000 as integer) as r into #t from bigtable 
-- CPU (1607) READS (204639) DURATION (1551)

go

select ABS(CAST(CAST(NEWID() AS VARBINARY) AS int)) % 10000 as r  into #t1 
from bigtable
-- Runs 15 times faster - CPU (78) READS (809) DURATION (99)

Profiler trace:

探查器跟踪：

alt text http://img519.imageshack.us/img519/8425/destroydbxu9.png

替代文字 http://img519.imageshack.us/img519/8425/destroydbxu9.png

This is proof that stuff is random enough for numbers between 0 to 9999

这证明对于 0 到 9999 之间的数字来说，东西是足够随机的

-- proof that stuff is random enough 
select avg(r) from #t
-- 5004
select STDEV(r) from #t
-- 2895.1999 

select avg(r) from #t1
-- 4992
select STDEV(r) from #t1
-- 2881.44 


select r,count(r) from #t
group by r 
-- 10000 rows returned 

select r,count(r) from #t1
group by r 
-- 10000 row returned 

回答by Joshua Carmody

Adam's answer works really well, so I marked it as accepted. While I was waiting for an answer though, I also found this blog entry with a few other (slightly less random) methods. Kaboing's method was among them.

亚当的回答非常有效，所以我将其标记为已接受。在我等待答案的同时，我还发现了这个博客条目以及其他一些（稍微不那么随机的）方法。Kaboing 的方法就是其中之一。

http://blog.sqlauthority.com/2007/04/29/sql-server-random-number-generator-script-sql-query/

http://blog.sqlauthority.com/2007/04/29/sql-server-random-number-generator-script-sql-query/

回答by denis_n

select RAND(CHECKSUM(NEWID()))

回答by Cindy Conway

I use c# for dealing with random numbers. It's much cleaner. I have a function I use to return a list of random number and a unique key, then I just join the uniqueKey on the row number. Because I use c#, I can easily specify a range within which the random numbers must fall.

我使用 c# 来处理随机数。干净多了。我有一个函数用来返回一个随机数列表和一个唯一键，然后我只在行号上加入 uniqueKey 。因为我使用 c#，所以我可以轻松指定随机数必须落入的范围。

Here are the steps to making the function: http://www.sqlwithcindy.com/2013/04/elegant-random-number-list-in-sql-server.html

以下是制作该功能的步骤：http: //www.sqlwithcindy.com/2013/04/elegant-random-number-list-in-sql-server.html

Here is what my query ends up looking like:

这是我的查询最终的样子：

SELECT 
   rowNumber, 
   name, 
   randomNumber
FROM dbo.tvfRandomNumberList(1,10,100) 
INNER JOIN (select ROW_NUMBER() over (order by int_id) as 'rowNumber', name from client        
            )as clients
ON clients.rowNumber = uniqueKey

回答by Cruachan

You might like to consider generating a UUID instead of a random number using the newid function. These are guaranteed to be unique each time generated whereas there is a significant chance that some duplication will occur with a straightforward random number (and depending on what you're using it for could give you a phenominally hard to debug error at a later point)

您可能想考虑使用 newid 函数生成 UUID 而不是随机数。这些保证每次生成时都是唯一的，而使用简单的随机数很可能会发生一些重复（并且根据您使用它的目的可能会在以后给您带来非常难以调试的错误）

回答by Adam

newid() i believe is very resource intensive. i recall trying that method on a table of a few million records and the performance wasn't nearly as good as rand().

newid() 我认为是资源密集型的。我记得在包含几百万条记录的表上尝试过这种方法，但性能几乎不如 rand()。

回答by Adam

According to my testing, the answer above doesn't generate a value of 10000 ever. This probably isn't much of a problem when you are generating a random between 1 and 10000, but the same algorithm between 1 and 5 would be noticable. Add 1 to your mod.

根据我的测试，上面的答案永远不会产生 10000 的值。当您生成 1 到 10000 之间的随机数时，这可能不是什么大问题，但是 1 到 5 之间的相同算法会很明显。将 1 添加到您的模组中。

回答by Ken

This snippet seems to provide a reasonable substitute for rand()in that it returns a float between 0.0 and 1.0. It uses only the last 3 bytes provided by newid()so total randomness may be slightly different than the conversion to VARBINARYthen INTthen modding from the recommended answer. Have not had a chance to test relative performance but seems fast enough (and random enough) for my purposes.

这个片段似乎提供了一个合理的替代rand()，因为它返回一个介于 0.0 和 1.0 之间的浮点数。它仅使用由提供的最后 3 个字节，newid()因此总随机性可能与转换到VARBINARY然后INT从推荐答案进行修改略有不同。没有机会测试相对性能，但对于我的目的来说似乎足够快（并且足够随机）。

SELECT CAST(SubString(CONVERT(binary(16), newid()), 14, 3) AS INT) / 16777216.0 AS R

回答by Adam

You need to use a UDF

您需要使用 UDF

first:

第一的：

CREATE VIEW vRandNumber
AS
SELECT RAND() as RandNumber

second:

第二：

CREATE FUNCTION RandNumber()
RETURNS float
AS
  BEGIN
  RETURN (SELECT RandNumber FROM vRandNumber)
  END

test:

测试：

SELECT dbo.RandNumber(), *
FROM <table>

Above borrowed from Jeff's SQL Server Blog

以上是从Jeff 的 SQL Server 博客中借来的