非递归可以的
with b as
(select level rn from dual connect by level<=10)
,t as
(select 1 l, '1' v from dual union all
select 2 l, '12' v from dual union all
select 2 l, '21' v from dual)
,t3 as
(select 3 l,
substr(v,1,rn-1)||(l+1)||substr(v,rn)
v from t,b
where t.l=2 and rn<=3
)
,t4 as
(select 4 l,rn,t.v v1,
substr(v,1,rn-1)||(l+1)||substr(v,rn)
--end
v from t3 t,b
where t.l=3 and rn<=4
)
select * from t4;
L RN V1 V
----- ---------- ------------------------------ ----------
4 1 312 4312
4 1 321 4321
4 2 312 3412
4 2 321 3421
4 3 312 3142
4 3 321 3241
4 4 312 3124
4 4 321 3214
4 1 132 4132
4 1 231 4231
4 2 132 1432
4 2 231 2431
4 3 132 1342
4 3 231 2341
4 4 132 1324
4 4 231 2314
4 1 123 4123
4 1 213 4213
4 2 123 1423
4 2 213 2413
4 3 123 1243
4 3 213 2143
4 4 123 1234
4 4 213 2134
择24行。
递归就出错
SQL> with b as
2 (select level rn from dual connect by level<=2)
3 ,a (l,v) as
4 (select 1 l, '1' v from dual
5 union all
6 select a.l+1 l,substr(v,1,rn-1)||(l+1)||substr(v,rn) v from a,b
7 where b.rn<=a.l
8 AND a.l<=3 -------- 给你加个限制
9 )
10 select * from a;
L V
---------- ----------
1 1
2 21
3 321
3 231
4 432
4 3421
4 423
4 2431
已选择8行。
已用时间: 00: 00: 03.00
SQL> 2
2* (select level rn from dual connect by level<=2)
SQL> c/2/3
2* (select level rn from dual connect by level<=3)
SQL> /
L V
---------- ----------
1 1
2 21
3 321
3 231
4 432
4 3421
4 3241
4 423
4 2431
4 2341
已选择10行。
已用时间: 00: 00: 00.09
[本帖最后由 〇〇 于 2010-6-11 16:08 编辑]
1626
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
