Oracle rand() 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27456683/
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
Oracle rand() function
提问by alvin Christianto
I tried to select a random data from table employees with query rand()but it can happen
我试图从带有查询的表员工中选择一个随机数据，rand()但它可能发生
SELECT email FROM employees
ORDER BY RAND()
LIMIT 1;
and the output is:
输出是：
ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
ORA-00933: SQL 命令没有正确结束
00933. 00000 - “SQL 命令没有正确结束”
Can somebody tell me why??
有人能告诉我为什么吗？？
回答by Sylvain Leroux
To speed-up operations on large table, you might use the SAMPLEclause to randomly extract a sample of data from your table, and then randomly pick one data from that sample:
为了加快对大表的操作，您可以使用该SAMPLE子句从您的表中随机提取数据样本，然后从该样本中随机选择一个数据：
select email from
( SELECT email,ROWNUM rn
FROM employees SAMPLE(5)
-- ^
-- each row has 5% chance of being picked-up
-- adjust that depending your table size and/or your needs
ORDER BY dbms_random.value)
where rn = 1;
An other idea is that you don't need a full sort just to extract onerandom row. For example, you might want to try that alternate approach:
另一个想法是，您不需要完全排序来提取一个随机行。例如，您可能想尝试这种替代方法：
with cte as (
SELECT email, ROWNUM rn
FROM employees
),
rnd as (
SELECT TRUNC(DBMS_RANDOM.VALUE(1, (SELECT COUNT(*) FROM CTE))) AS value FROM DUAL
)
SELECT cte.email FROM cte JOIN rnd
ON cte.rn = rnd.value;
I don't know if Oracle is able to "properly" optimize such queries though.
我不知道 Oracle 是否能够“正确”优化此类查询。
回答by Gordon Linoff
The Oracle equivalent of rand()is dbms_random.value.
甲骨文相当于rand()是dbms_random.value。
The Oracle equivalent of limitis either a subquery with rownumor (in Oracle 12) fetch first xx row only. So, one of these should work:
Oracle 等价物limit是带有rownumor的子查询（在 Oracle 12 中）fetch first xx row only。因此，其中之一应该有效：
select email
from employees
order by dbms_random.value
fetch first 1 row only;
or:
或者：
select email
from (select email
from employees
order by dbms_random.value
) e
where rownum = 1
回答by Multisync
Oracle doesn't have LIMIT. You can try this:
Oracle 没有限制。你可以试试这个：
select email from
(SELECT email, row_number() over(order by dbms_random.value) rw FROM employees)
where rw = 1;
回答by aeron
SELECT
(DBMS_RANDOM.VALUE( 0, 1 )) as RND,
....
....
....
From (your_table)
Order by RND
