sql - 使用随机值作为连接条件

Question

我正在生成一些测试数据并使用 dbms_random .我在使用 dbms_random 时遇到了一些奇怪的行为在 JOIN 的条件下，我无法解释:

------------------------# test-data (ids 1 .. 3)
With x As (
  Select Rownum id From dual
  Connect By Rownum <= 3
)
------------------------# end of test-data
Select x.id,
       x2.id id2
  From x
  Join x x2 On ( x2.id = Floor(dbms_random.value(1, 4)) )

Floor(dbms_random.value(1, 4) )返回 (1,2,3) 中的一个随机数，所以我期望 x 的所有行加入随机一行 x2 ，或者可能总是相同的随机行 x2如果随机数只计算一次。

但是，多次尝试时，我得到了这样的结果:

(1)   ID  ID2        (2)   ID  ID2        (3)
    ---- ----            ---- ----            no rows selected.
       1    2               1    3
       1    3               2    3
       2    2               3    3
       2    3
       3    2
       3    3

我错过了什么？

编辑 :

SELECT ROWNUM, FLOOR(dbms_random.VALUE (1, 4))
FROM dual CONNECT BY ROWNUM <= 3

在这种情况下会得到结果，但是为什么原始查询会这样呢？

Answer 1

要生成具有一个可预测值和一个随机值的三行，请尝试以下操作:

SQL> with x as (
  2    select rownum id from dual
  3    connect by rownum <= 3
  4      )
  5      , y as (
  6    select floor(dbms_random.value(1, 4)) floor_val
  7    from dual
  8      )
  9  select x.id,
 10         y.floor_val
 11  from x
 12  cross join y
 13  /

        ID  FLOOR_VAL
---------- ----------
         1          2
         2          3
         3          2

SQL

编辑

为什么您的原始查询返回一组不一致的行？

好吧，如果没有 ON 子句中的随机位，您的查询基本上是 X 与 X 的 CROSS JOIN - 它会返回九行(至少如果语法允许的话，它会返回)。这九行中的每一行都执行对 DBMS_RANDOM.VALUE() 的调用。 .只有当随机值与 X2.ID 的当前值匹配时，才包含在结果集中的行。因此，查询可以随机返回 0-9 行。

您的解决方案显然更简单-我没有进行足够的重构:)