SQL 生成 1 - 10 范围内的随机数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1400505/
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 a random number in the range 1 - 10
提问by KB22
Since my approach for a test query which I worked on in this questiondid not work out, I'm trying something else now. Is there a way to tell pg's random()function to get me only numbers between 1 and 10?
由于我在这个问题中处理的测试查询方法没有奏效，我现在正在尝试其他方法。有没有办法告诉 pg 的random()函数只让我得到 1 到 10 之间的数字？
回答by
If by numbers between 1 and 10 you mean any float that is >= 1 and < 10, then it's easy:
如果通过 1 到 10 之间的数字表示任何 >= 1 和 < 10 的浮点数，那么这很容易：
select random() * 9 + 1
This can be easily tested with:
这可以通过以下方式轻松测试：
# select min(i), max(i) from (
select random() * 9 + 1 as i from generate_series(1,1000000)
) q;
min | max
-----------------+------------------
1.0000083274208 | 9.99999571684748
(1 row)
If you want integers, that are >= 1 and < 10, then it's simple:
如果你想要整数，即 >= 1 和 < 10，那么很简单：
select trunc(random() * 9 + 1)
And again, simple test:
再一次，简单的测试：
# select min(i), max(i) from (
select trunc(random() * 9 + 1) as i from generate_series(1,1000000)
) q;
min | max
-----+-----
1 | 9
(1 row)
回答by vanburg
To summarize and a bit simplify, you can use:
总结并稍微简化一下，您可以使用：
-- 0 - 9
select floor(random() * 10);
-- 0 - 10
SELECT floor(random() * (10 + 1));
-- 1 - 10
SELECT ceil(random() * 10);
And you can test this like mentioned by @user80168
你可以像@user80168提到的那样测试这个
-- 0 - 9
SELECT min(i), max(i) FROM (SELECT floor(random() * 10) AS i FROM generate_series(0, 100000)) q;
-- 0 - 10
SELECT min(i), max(i) FROM (SELECT floor(random() * (10 + 1)) AS i FROM generate_series(0, 100000)) q;
-- 1 - 10
SELECT min(i), max(i) FROM (SELECT ceil(random() * 10) AS i FROM generate_series(0, 100000)) q;
回答by Neha Jain
If you are using SQL Server then correct way to get integer is
如果您使用的是 SQL Server，那么获取整数的正确方法是
SELECT Cast(RAND()*(b-a)+a as int);
Where
在哪里
- 'b' is the upper limit
- 'a' is lower limit
- 'b' 是上限
- 'a' 是下限
回答by hythlodayr
(trunc(random() * 10) % 10) + 1
(trunc(random() * 10) % 10) + 1
回答by mythicalcoder
The correct version of hythlodayr's answer.
hythlodayr 答案的正确版本。
-- ERROR: operator does not exist: double precision % integer
-- LINE 1: select (trunc(random() * 10) % 10) + 1
The output from trunchas to be converted to INTEGER. But it can be done without trunc. So it turns out to be simple.
的输出trunc必须转换为INTEGER. 但它可以在没有trunc. 所以结果很简单。
select (random() * 9)::INTEGER + 1
Generates an INTEGER output in range [1, 10] i.e. both 1 & 10 inclusive.
生成范围 [1, 10] 内的 INTEGER 输出，即 1 和 10 都包括在内。
For any number (floats), see user80168's answer. i.e just don't convert it to INTEGER.
对于任何数字（浮点数），请参阅 user80168 的答案。即只是不要将其转换为INTEGER.
回答by Abu Khalil Mohamed
This stored procedure inserts a rand number into a table. Look out, it inserts an endless numbers. Stop executing it when u get enough numbers.
此存储过程将一个 rand 数字插入到表中。注意，它插入了无穷无尽的数字。当你得到足够的数字时停止执行它。
create a table for the cursor:
为游标创建一个表：
CREATE TABLE [dbo].[SearchIndex](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Cursor] [nvarchar](255) NULL)
GO
走
Create a table to contain your numbers:
创建一个表格来包含您的数字：
CREATE TABLE [dbo].[ID](
[IDN] [int] IDENTITY(1,1) NOT NULL,
[ID] [int] NULL)
INSERTING THE SCRIPT :
插入脚本：
INSERT INTO [SearchIndex]([Cursor]) SELECT N'INSERT INTO ID SELECT FLOOR(rand() * 9 + 1) SELECT COUNT (ID) FROM ID
CREATING AND EXECUTING THE PROCEDURE:
创建和执行程序：
CREATE PROCEDURE [dbo].[RandNumbers] AS
BEGIN
Declare CURSE CURSOR FOR (SELECT [Cursor] FROM [dbo].[SearchIndex] WHERE [Cursor] IS NOT NULL)
DECLARE @RandNoSscript NVARCHAR (250)
OPEN CURSE
FETCH NEXT FROM CURSE
INTO @RandNoSscript
WHILE @@FETCH_STATUS IS NOT NULL
BEGIN
Print @RandNoSscript
EXEC SP_EXECUTESQL @RandNoSscript;
END
END
GO
Fill your table:
填写您的表格：
EXEC RandNumbers
回答by leejaycoke
Actually I don't know you want to this.
其实我不知道你想要这个。
try this
尝试这个
INSERT INTO my_table (my_column)
SELECT
(random() * 10) + 1
;
