SQL 在 Oracle Query 中的每一行中生成随机数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1568630/
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
Generating Random Number In Each Row In Oracle Query
提问by Saobi
I want to select all rows of a table followed by a random number between 1 to 9:
我想选择表的所有行，然后是 1 到 9 之间的随机数：
select t.*, (select dbms_random.value(1,9) num from dual) as RandomNumber
from myTable t
But the random number is the same from row to row, only different from each run of the query. How do I make the number different from row to row in the same execution?
但是随机数在行与行之间是相同的，只是查询的每次运行不同。如何在同一执行中使行与行之间的数字不同？
回答by toolkit
Something like?
就像是？
select t.*, round(dbms_random.value() * 8) + 1 from foo t;
Edit: David has pointed out this gives uneven distribution for 1 and 9.
编辑：大卫指出这给出了 1 和 9 的不均匀分布。
As he points out, the following gives a better distribution:
正如他所指出的，以下给出了更好的分布：
select t.*, floor(dbms_random.value(1, 10)) from foo t;
回答by David Aldridge
At first I thought that this would work:
起初我认为这会起作用：
select DBMS_Random.Value(1,9) output
from ...
However, this does not generate an even distribution of output values:
但是，这不会产生均匀分布的输出值：
select output,
count(*)
from (
select round(dbms_random.value(1,9)) output
from dual
connect by level <= 1000000)
group by output
order by 1
1 62423
2 125302
3 125038
4 125207
5 124892
6 124235
7 124832
8 125514
9 62557
The reasons are pretty obvious I think.
我认为原因很明显。
I'd suggest using something like:
我建议使用类似的东西：
floor(dbms_random.value(1,10))
Hence:
因此：
select output,
count(*)
from (
select floor(dbms_random.value(1,10)) output
from dual
connect by level <= 1000000)
group by output
order by 1
1 111038
2 110912
3 111155
4 111125
5 111084
6 111328
7 110873
8 111532
9 110953
回答by Harry Phillips
If you just use round then the two end numbers (1 and 9) will occur less frequently, to get an even distribution of integers between 1 and 9 then:
如果您只使用 round，那么两个结束数字（1 和 9）的出现频率将降低，以获得 1 和 9 之间整数的均匀分布，然后：
SELECT MOD(Round(DBMS_RANDOM.Value(1, 99)), 9) + 1 FROM DUAL
回答by knittl
you don't need a select … from dual, just write:
你不需要一个select … from dual，只要写：
SELECT t.*, dbms_random.value(1,9) RandomNumber
FROM myTable t
