生成范围为1 - 10的随机数

Since my approach for a test query which I worked on in this question did 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之间的数字?

7 个解决方案

#1

131

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)

#2

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;

#3

If you are using SQL Server then correct way to get integer is

如果您正在使用SQL Server，那么获得integer的正确方法是

SELECT Cast(RAND()*(b-a)+a as int);

Where

在哪里

'b' is the upper limit
b是上限
'a' is lower limit
“一”是下限

#4

(trunc(random() * 10) % 10) + 1

(trunc(random() * 10) % 10) + 1。

#5

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 trunc has to be converted to INTEGER. But it can be done without trunc. So it turns out to be simple.

trunc的输出必须转换为整数。但它可以在没有trunc的情况下完成。结果很简单。

select (random() * 9)::INTEGER + 1

Generates an INTEGER output in range [1, 10] i.e. both 1 & 10 inclusive.

生成范围为[1,10]的整数输出，即包括1和10。

For any number (floats), see user80168's answer. i.e just don't convert it to INTEGER.

对于任何数字(浮点数)，请参见user80168的答案。我。e只是不要把它变成整数。

#6

Actually I don't know you want to this.

实际上我不知道你想这么做。

try this

试试这个

INSERT INTO my_table (my_column)
SELECT
    (random() * 10) + 1
;

#7

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)

去

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

#1

131