英文:
Add a different random row from table A to each row in Table B
问题
我有两个表，一个叫做A，另一个叫做B，
我想要将表A中的每一行添加一个不同的随机行到表B中的每一行。
这两个表之间没有共同的字段。
我尝试过以下操作：
select idA, idB
from A, (select idB
from B
Order by dbms_random.random
fetch first 1 row only)
但是，在'from'子句中的查询只执行了一次。因此，我得到了来自表B的相同行添加到表A的所有行中。
谢谢。
英文:
I have tow tables the one named A and the second named B,
I want to add a a **different ** random row from table A to each row in Table B.
There are no cummon fields between the tables.
I tried to do:
select idA,idB
from A,(select idB
from B
Order by dbms_random.random
fetch first 1 row only)
But, the select in the 'from' executed only once. So, I got the same row from table B to all rows in table A.
Thanks.
答案1
得分: 2
你可以使用侧连接（lateral join）。根据dbms_random.random函数猜测你正在使用Oracle数据库。
在Oracle中，你可以这样做：
select A.idA, x.idB
from A,
lateral (
select idB
from B
Order by dbms_random.random + A.idA * 0
fetch first 1 row only
) x
注意：为了使侧连接对每一行都运行，你需要为它与主表创建一个“依赖关系”。在这种情况下，我们可以通过添加 + A.idA * 0 来欺骗Oracle；这不会产生任何副作用，除了让Oracle认为侧连接的行依赖于主表。
查看运行示例。
英文:
You can use a lateral join. Guessing by dbms_random.random function you are using the Oracle database.
In Oracle you can do:
select A.idA, x.idB
from A,
lateral (
select idB
from B
Order by dbms_random.random + A.idA * 0
fetch first 1 row only
) x
Note: For the lateral join to be run for every single row you need to produce a "dependency" from it to the main table. In this case we can trick Oracle by adding + A.idA * 0; this doesn't have any side effect, except that Oracle thinks the lateral row depends on the primary one.
See running example.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论