SQL 生成从 3 到 6 的随机整数值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7878287/
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
Generate random int value from 3 to 6
提问by FSou1
Is it possible in Microsoft SQL Server generate random int value from Min to Max (3-9 example, 15-99 e.t.c)
是否可以在 Microsoft SQL Server 中生成从 Min 到 Max 的随机整数值（3-9 示例，15-99 等）
I know, i can generate from 0 to Max, but how to increase Min border?
我知道，我可以从 0 生成到最大，但是如何增加最小边界？
This query generate random value from 1 to 6. Need to change it from 3 to 6.
此查询生成从 1 到 6 的随机值。需要将其从 3 更改为 6。
SELECT table_name, 1.0 + floor(6 * RAND(convert(varbinary, newid()))) magic_number
FROM information_schema.tables
Added 5 sec later:
5 秒后添加：
Stupid question, sorry...
愚蠢的问题，对不起...
SELECT table_name, 3.0 + floor(4 * RAND(convert(varbinary, newid()))) magic_number
FROM information_schema.tables
回答by orgtigger
This generates a random number between 0-9
这会生成一个 0-9 之间的随机数
SELECT ABS(CHECKSUM(NEWID()) % 10)
1 through 6
1 到 6
SELECT ABS(CHECKSUM(NEWID()) % 6) + 1
3 through 6
3 到 6
SELECT ABS(CHECKSUM(NEWID()) % 4) + 3
Dynamic (Based on Eilert Hjelmeseths Comment)
动态（基于 Eilert Hjelmeseths 评论）
SELECT ABS(CHECKSUM(NEWID()) % (@max - @min + 1)) + @min
Updated based on comments:
根据评论更新：
NEWIDgenerates random string (for each row in return)CHECKSUMtakes value of string and creates number- modulus (
%) divides by that number and returns the remainder (meaning max value is one less than the number you use) ABSchanges negative results to positive- then add one to the result to eliminate 0 results (to simulate a dice roll)
NEWID生成随机字符串（对于每一行作为回报）CHECKSUM获取字符串的值并创建数字- 模数 (
%) 除以该数字并返回余数（意味着最大值比您使用的数字小一） ABS将负面结果变为正面- 然后在结果中加 1 以消除 0 个结果（模拟掷骰子）
回答by Martin Smith
I see you have added an answer to your question in SQL Server 2008 you can also do
我看到您在 SQL Server 2008 中添加了您的问题的答案，您也可以这样做
SELECT 3 + CRYPT_GEN_RANDOM(1) % 4 /*Random number between 3 and 6*/
FROM ...
A couple of disadvantages of this method are
这种方法的几个缺点是
- This is slower than the
NEWID()method - Even though it is evaluated once per row the query optimiser does not realise this which can lead to odd results.
- 这比
NEWID()方法慢 - 即使每行计算一次，查询优化器也没有意识到这一点，这可能会导致奇怪的结果。
but just thought I'd add it as another option.
但只是想我会将它添加为另一种选择。
回答by Lamak
You can do this:
你可以这样做：
DECLARE @maxval TINYINT, @minval TINYINT
select @maxval=24,@minval=5
SELECT CAST(((@maxval + 1) - @minval) *
RAND(CHECKSUM(NEWID())) + @minval AS TINYINT)
And that was taken directly from this link, I don't really know how to give proper credit for this answer.
这是直接从这个链接中获取的，我真的不知道如何对这个答案给予适当的评价。
回答by Adrian Carr
Nice and simple, from Pinal Dave's site:
很好很简单，来自 Pinal Dave 的网站：
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/
DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT
SET @Lower = 3 ---- The lowest random number
SET @Upper = 7 ---- One more than the highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random
(I did make a slight change to the @Upper- to include the upper number, added 1.)
（我确实对@Upper- 进行了细微的更改以包含上限数字，并添加了 1。）
回答by MERT DO?AN
Simply:
简单地：
DECLARE @MIN INT=3; --We define minimum value, it can be generated.
DECLARE @MAX INT=6; --We define maximum value, it can be generated.
SELECT @MIN+FLOOR((@MAX-@MIN+1)*RAND(CONVERT(VARBINARY,NEWID()))); --And then this T-SQL snippet generates an integer between minimum and maximum integer values.
You can change and edit this code for your needs.
您可以根据需要更改和编辑此代码。
回答by Manjunath Bilwar
Here is the simple and single line of code
这是简单的单行代码
For this use the SQL Inbuild RAND()function.
为此，请使用 SQL Inbuild RAND()函数。
Here is the formula to generate random number between two number (RETURN INT Range)
这是在两个数字之间生成随机数的公式（RETURN INT Range）
Here a is your First Number (Min) and b is the Second Number (Max) in Range
这里 a 是您的第一个数字（最小值），b 是范围中的第二个数字（最大值）
SELECT FLOOR(RAND()*(b-a)+a)
Note: You can use CAST or CONVERT function as well to get INT range number.
注意：您也可以使用 CAST 或 CONVERT 函数来获取 INT 范围编号。
( CAST(RAND()*(25-10)+10 AS INT) )
( CAST(RAND()*(25-10)+10 AS INT) )
Example:
例子：
SELECT FLOOR(RAND()*(25-10)+10);
Here is the formula to generate random number between two number (RETURN DECIMAL Range)
这是在两个数字之间生成随机数的公式（RETURN DECIMAL Range）
SELECT RAND()*(b-a)+a;
Example:
例子：
SELECT RAND()*(25-10)+10;
More details check this: https://www.techonthenet.com/sql_server/functions/rand.php
更多细节请查看：https: //www.techonthenet.com/sql_server/functions/rand.php
回答by greg121
SELECT ROUND((6 - 3 * RAND()), 0)
回答by Zeinab
In general:
一般来说：
select rand()*(@upper-@lower)+@lower;
For your question:
对于您的问题：
select rand()*(6-3)+3;
<=>
<=>
select rand()*3+3;
回答by AndreFeijo
Lamak'sanswer as a function:
拉马克作为函数的回答：
-- Create RANDBETWEEN function
-- Usage: SELECT dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID())))
CREATE FUNCTION dbo.RANDBETWEEN(@minval TINYINT, @maxval TINYINT, @random NUMERIC(18,10))
RETURNS TINYINT
AS
BEGIN
RETURN (SELECT CAST(((@maxval + 1) - @minval) * @random + @minval AS TINYINT))
END
GO
回答by Mike de Klerk
DECLARE @min INT = 3;
DECLARE @max INT = 6;
SELECT @min + ROUND(RAND() * (@max - @min), 0);
Step by step
一步步
DECLARE @min INT = 3;
DECLARE @max INT = 6;
DECLARE @rand DECIMAL(19,4) = RAND();
DECLARE @difference INT = @max - @min;
DECLARE @chunk INT = ROUND(@rand * @difference, 0);
DECLARE @result INT = @min + @chunk;
SELECT @result;
Note that a user-defined function thus not allow the use of RAND(). A workaround for this (source: http://blog.sqlauthority.com/2012/11/20/sql-server-using-rand-in-user-defined-functions-udf/) is to create a view first.
请注意，用户定义的函数因此不允许使用 RAND()。对此的解决方法（来源：http: //blog.sqlauthority.com/2012/11/20/sql-server-using-rand-in-user-defined-functions-udf/）是首先创建一个视图。
CREATE VIEW [dbo].[vw_RandomSeed]
AS
SELECT RAND() AS seed
and then create the random function
然后创建随机函数
CREATE FUNCTION udf_RandomNumberBetween
(
@min INT,
@max INT
)
RETURNS INT
AS
BEGIN
RETURN @min + ROUND((SELECT TOP 1 seed FROM vw_RandomSeed) * (@max - @min), 0);
END
