一个SQL问题（寻求解答中）

私水流年

newkid 发表于 2012-3-23 22:00
什么版本？改用prior sys_guid() is not null怎么样？

验证了一下,应该是在11.1版本下会报 -01436错误
例如
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE        11.1.0.6.0        Production

TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

但是在11.2版本下不会.

weixinchao2012

newkid 发表于 2011-3-2 23:29
你能理解CONNECT BY吧？它相当于是一个递归的自连接，不断地把每层的连接结果叠加到结果集中。两层之间的连 ...

ora_pascal1982

newkid 发表于 2011-3-2 23:29
你能理解CONNECT BY吧？它相当于是一个递归的自连接，不断地把每层的连接结果叠加到结果集中。两层之间的连 ...

终于明白了

hwa12340

学习了！赞！

fuzhangpeng

来迟了。。。。
SQL> select * from t1;

        ID NAME
---------- ------------------------------------------------------------
         1 123
         2 bb
         3 dd

SQL> SELECT * FROM t1     
  connect by  level <=id        
  and    Prior id = id
  And Prior dbms_random.value() Is Not Null;

        ID NAME
---------- ------------------------------------------------------------
         1 123
         2 bb
         2 bb
         3 dd
         3 dd
         3 dd

6 rows selected.

yaoweber

我比较笨，只懂的用笨方法。希望可以帮到你。
这里是构造表

1. CREATE TABLE test_weber2(ID NUMBER,NAME VARCHAR2(12),sex VARCHAR2(2));
   
2. 
   
3. INSERT INTO test_weber2 VALUES(3,'aaa','F');
   
4. INSERT INTO test_weber2 VALUES(4,'aaa','M');
   
5. SELECT * FROM TEST_WEBER2

复制代码

这里是代码

1. 
   
2. DECLARE
   
3.   CURSOR CUR1 IS
   
4.     SELECT ID FROM TEST_WEBER2;
   
5.   V_CNT NUMBER;
   
6.   V_NUM NUMBER := 1;
   
7. 
   
8. BEGIN
   
9.   FOR C IN CUR1 LOOP
   
10.     V_CNT := C.ID;
    
11.     WHILE V_NUM <= V_CNT LOOP
    
12.       INSERT INTO TEST_WEBER2
    
13.       VALUES
    
14.         (V_NUM,
    
15.          (SELECT NAME FROM TEST_WEBER2 WHERE ID = V_CNT),
    
16.          (SELECT SEX FROM TEST_WEBER2 WHERE ID = V_CNT));
    
17.       V_NUM := V_NUM + 1;
    
18.     END LOOP;
    
19.     V_NUM :=1;
    
20.   END LOOP;
    
21.   COMMIT;
    
22. END;
    

复制代码

stevendba

SQL> with t as (
  2  select 3 id, 'a' val from dual union all
  3  select 4 id, 'b' val from dual )
  4  select distinct level,val from t connect by level <= id
  5  order by val ;

     LEVEL VA
---------- --
         1 a
         2 a
         3 a
         1 b
         2 b
         3 b
         4 b

newkid

stevendba 发表于 2015-3-19 19:56
SQL> with t as (
  2  select 3 id, 'a' val from dual union all
  3  select 4 id, 'b' val from dual ...

这种写法很低效，前后级之间是笛卡尔积，中间数据太多了。

stevendba

newkid 发表于 2015-3-19 21:18
这种写法很低效，前后级之间是笛卡尔积，中间数据太多了。

受教了！

lastwinner

newkid 发表于 2011-3-3 23:07
你这个语句根本没有前后级的连接条件，只有中止循环的条件LEVEL

不知道现在回复是不是有一点晚，我想现在你肯定理解这个connect by level<x的写法了
它其实是以任意一条记录为根，然后寻找原集合中任意一条记录为下一节点，周而复始，直到level<x不成立时方终止递归

加上level<x的约束后，level就不会无限制的扩大。

14:07:48 SCOTT@Z3> select id,name,level, sys_connect_by_path(name,',') sc, connect_by_root name rootname from one connect by level<4;

ID         NAME            LEVEL SC                   ROOTNAME
---------- ---------- ---------- -------------------- --------------------
9          96                  1 ,96                  96
9          96                  2 ,96,96               96
9          96                  3 ,96,96,96            96
123        22                  3 ,96,96,22            96
123        22                  2 ,96,22               96
9          96                  3 ,96,22,96            96
123        22                  3 ,96,22,22            96
123        22                  1 ,22                  22
9          96                  2 ,22,96               22
9          96                  3 ,22,96,96            22
123        22                  3 ,22,96,22            22
123        22                  2 ,22,22               22
9          96                  3 ,22,22,96            22
123        22                  3 ,22,22,22            22
14 rows selected.

如果只加上 prior name<>name的条件，无nocycle，则oracle没有办法限制下一个路径中的对象和以前的不重复，所以就会报错。
14:10:31 SCOTT@Z3> select id,name,level, sys_connect_by_path(name,',') sc, connect_by_root name rootname from one connect by level<4 and prior name<>name;
ERROR:
ORA-01436: 用户数据中的 CONNECT BY 循环



no rows selected

但如果将level<4改为level<3则可以执行成功，不指定nocycle也能成功，这是由于数据的特点导致的。
14:10:44 SCOTT@Z3> select id,name,level, sys_connect_by_path(name,',') sc, connect_by_root name rootname from one connect by level<3 and prior name<>name;

ID         NAME            LEVEL SC                   ROOTNAME
---------- ---------- ---------- -------------------- --------------------
9          96                  1 ,96                  96
123        22                  2 ,96,22               96
123        22                  1 ,22                  22
9          96                  2 ,22,96               22


select id,name,level, sys_connect_by_path(name,',') sc, connect_by_root name rootname from one connect by nocycle level<4 and prior name<>name;
这一句的结果和上面相同