|
SQL> SQL> select :n from dual;
:N
----------
8
已用时间: 00: 00: 00.00
SQL> 15
SP2-0226: 行号 无效
SQL> WITH b AS (
2 SELECT REPLACE(SYS_CONNECT_BY_PATH(n,','),',') str
3 FROM (SELECT 1 n FROM DUAL UNION ALL SELECT 0 FROM DUAL)
4 WHERE LEVEL=:N
5 CONNECT BY LEVEL<=:N
6 )
7 ,t(lvl,all_str,str) AS (
8 SELECT 1,CAST(str AS VARCHAR2(30)),str FROM b
9 UNION ALL
10 SELECT t.lvl+1
11 ,t.all_str||b.str
12 ,b.str
13 FROM t,b
14 WHERE t.lvl<:N
15 AND t.str<b.str and substr(t.str,1,2)<>substr(b.str,1,2)
16 AND NOT EXISTS (SELECT 1
17 FROM DUAL
18 WHERE NVL(REPLACE(LPAD(SUBSTR(t.all_str,(LEVEL-1)*:N+1,:N)+b.str,:N,'0'),'1'),'0')
19 NOT IN ('0','2','20','02')
20 CONNECT BY LEVEL<=t.lvl
21 )
22 )
23 SELECT all_str FROM t WHERE lvl=:N;
未选定行
已用时间: 00: 00: 01.96
SQL> SQL> WITH b AS (
2 SELECT REPLACE(SYS_CONNECT_BY_PATH(n,','),',') str
3 FROM (SELECT 1 n FROM DUAL UNION ALL SELECT 0 FROM DUAL)
4 WHERE LEVEL=:N
5 CONNECT BY LEVEL<=:N
6 )
7 ,t(lvl,all_str,str) AS (
8 SELECT 1,CAST(str AS VARCHAR2(30)),str FROM b
9 UNION ALL
10 SELECT t.lvl+1
11 ,t.all_str||b.str
12 ,b.str
13 FROM t,b
14 WHERE t.lvl<:N
15 AND t.str<b.str --and substr(t.str,1,2)<>substr(b.str,1,2)
16 AND NOT EXISTS (SELECT 1
17 FROM DUAL
18 WHERE NVL(REPLACE(LPAD(SUBSTR(t.all_str,(LEVEL-1)*:N+1,:N)+b.str,:N,'0'),'1'),'0')
19 NOT IN ('0','2','20','02')
20 CONNECT BY LEVEL<=t.lvl
21 )
22 )
23 SELECT all_str FROM t WHERE lvl=:N;
未选定行
已用时间: 00: 00: 03.22 |
|