oracle 为 SQL 连接选择单个（随机）行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1487004/
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
Selecting a single (random) row for an SQL join
提问by PaulJWilliams
I've got an sql query that selects data from several tables, but I only want to match a single(randomly selected) row from another table.
我有一个从多个表中选择数据的 sql 查询，但我只想匹配另一个表中的单个（随机选择的）行。
Easier to show some code, I guess ;)
我想更容易显示一些代码;)
Table K is (k_id, selected) Table C is (c_id, image) Table S is (c_id, date) Table M is (c_id, k_id, score)
表K是（k_id，选择）表C是（c_id，图像）表S是（c_id，日期）表M是（c_id，k_id，分数）
All ID-columns are primary keys, with appropriate FK constraints.
所有 ID 列都是主键，具有适当的 FK 约束。
What I want, in english, is for eack row in K that has selected = 1 to get a random row from C where there exists a row in M with (K_id, C_id), where the score is higher than a given value, and where c.image is not null and there is a row in s with c_id
用英语来说，我想要的是 K 中的 eack 行，它选择了 = 1 从 C 中获取一个随机行，其中 M 中存在一行（K_id，C_id），其中分数高于给定值，并且其中 c.image 不为空并且 s 中有一行带有 c_id
Something like:
就像是：
select k.k_id, c.c_id, m.score
from k,c,m,s
where k.selected = 1
and m.score > some_value
and m.k_id = k.k_id
and m.c_id = c.c_id
and c.image is not null
and s.c_id = c.c_id;
The only problem is this returns all the rows in C that match the criteria - I only want one...
唯一的问题是这会返回 C 中符合条件的所有行 - 我只想要一个......
I can see how to do it using PL/SQL to select all relevent rows into a collection and then select a random one, but I'm stuck as to how to select a random one.
我可以看到如何使用 PL/SQL 将所有相关行选择到一个集合中，然后选择一个随机行，但我对如何选择一个随机行感到困惑。
采纳答案by Vincent Malgrat
with analytics:
分析：
SELECT k_id, c_id, score
FROM (SELECT k.k_id, c.c_id, m.score,
row_number() over(PARTITION BY k.k_id ORDER BY NULL) rk
FROM k, c, m, s
WHERE k.selected = 1
AND m.score > some_value
AND m.k_id = k.k_id
AND m.c_id = c.c_id
AND c.image IS NOT NULL
AND s.c_id = c.c_id)
WHERE rk = 1
This will select one row that satisfies your criteria per k_id. This will likely select the same set of rows if you run the query several times. If you want more randomness (each run produces a different set of rows), you would replace ORDER BY NULLby ORDER BY dbms_random.value
这将选择满足每个 k_id 条件的一行。如果您多次运行查询，这可能会选择相同的行集。如果您想要更多的随机性（每次运行产生一组不同的行），您将替换ORDER BY NULL为ORDER BY dbms_random.value
回答by Andre Pastore
you can use the 'order by dbms_random.random' instruction with your query.
您可以在查询中使用“order by dbms_random.random”指令。
i.e.:
IE：
SELECT column FROM
(
SELECT column FROM table
ORDER BY dbms_random.value
)
WHERE rownum = 1
References: http://awads.net/wp/2005/08/09/order-by-no-order/http://www.petefreitag.com/item/466.cfm
参考资料：http: //awads.net/wp/2005/08/09/order-by-no-order/ http://www.petefreitag.com/item/466.cfm
回答by Marius
I'm not too familiar with oracle SQL, but try using LIMIT random(), if there is such a function available.
我对 oracle SQL 不太熟悉，但是如果有这样的函数可用，请尝试使用 LIMIT random()。
