rownum是多余的
with t as (
select 'A' id,3 cnt from dual
union all
select 'B' id,2 cnt from dual
union all
select 'C' id,5 cnt from dual
)
select id, 1
from t
connect by level <= cnt
and prior cnt = cnt
and prior dbms_random.value is not null;
这么写也对。
按照人的思维，
connect by level <= cnt
and prior cnt = cnt
这两个就对了，但是会报错。oracle有一套自己的判断loop的方法。
"if the CONNECT BY condition results in a loop in the hierarchy,then Oracle returns an error.A loop occurs if one row is both the parent(or grandparent or direct ancestor)
and a child(or a grandchild or a direct descendent) of anothor row"
and prior dbms_random.value is not null;可以混淆这个判断。具体原理我也没找到准确的解释。
另外在有些版本里必须用with才能避免loop，有些则可以写下边，甚至直接查表。
select id, 1
from (select 'A' id, 3 cnt
from dual
union all
select 'B' id, 2 cnt
from dual
union all
select 'C' id, 5 cnt from dual)
connect by level <= cnt
and prior cnt = cnt
and prior dbms_random.value is not null;
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi报错
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi， Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production可以
|
