如何在oracle SQL中将一个表中的行随机分配给另一个表中的行？

Question

我试图找出一个SQL查询，该查询将随机地将记录从一个表分发到另一个表。

例如:我有一张顾客表，我想从一张车表中给每个人分配一辆车。

我想确保汽车是随机分布的，但是没有顾客的财产可以预测他们会收到哪辆车。

客户：(乔恩，山姆，萨拉，杰克，亚当，禤浩焯)

汽车：(宝马、道奇、雷克萨斯)

结果：

(乔恩-宝马，萨姆-雷克萨斯，萨拉-宝马，杰克-道奇，亚当-道奇，禤浩焯-宝马)

如何在Oracle SQL中做到这一点？

Answer 1

这里有一个选择：

SQL> with t as
  2    (select u.name ||'-'||a.name comb,
  3       row_number() over (partition by u.name order by dbms_random.value(1, n.cnt)) rn
  4     from customers u cross join cars a
  5     join (select count(*) cnt from cars) n on 1 = 1
  6    )
  7  select t.comb
  8  from t
  9  where rn = 1;

COMB
-----------------------------------------
Adam-Lexus
Adrian-BMW
Jack-Lexus
Jon-BMW
Sam-Dodge
Sara-Lexus

6 rows selected.

SQL>

Answer 2

一种可能比完全交叉连接更有效的方法是：

select c.*, cc.car
from (select c.*,
             row_number() over (order by dbms_random.value(1, cc.cnt) as seqnum
      from customers c cross join
           (select count(*) as cnt from cars) cc
     ) c join
     (select cc.*, row_number() over (order by dbms_random.random) as seqnum
      from cars cc
     ) cc
     on cc.seqnum = c.seqnum;

Answer 3

如果没有限制使用所有cars和DB资源：

select customer_name||'-'||car_name result 
from (
    select u.name customer_name, c.name car_name, 
        row_number() over ( partition by u.name order by dbms_random.value ) ord
    from customers u
    cross join cars c
)
where ord = 1