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

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;
这一句的结果和上面相同

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;
这一句的结果和上面相同

newkid

lastwinner 发表于 2017-3-20 14:21
不知道现在回复是不是有一点晚，我想现在你肯定理解这个connect by level

呵呵，你这是随机挖坟，顺便给我上一课？
关于LEVEL的用法，从来都没有疑问。
当时不明白的是，为什么有时候ORACLE会报ORA-01436，而仅仅有LEVEL条件的时候却不报？如果仅有LEVEL条件，那么重复节点在第二层就会出现！你现在的说法仍然无法解释这一现象。
我现在根据多年的观察有了一个说法。你搞明白了没？

lastwinner

newkid 发表于 2017-3-20 21:46
呵呵，你这是随机挖坟，顺便给我上一课？
关于LEVEL的用法，从来都没有疑问。
当时不明白的是，为什么 ...

跟随帖子一步一步走到这里的，非随机模式
我就觉得下午说的还不够清晰，现在再补充一下：
仅有level时，oracle并不关注数据是否会出现重复，而当出现prior x<>x时，oracle就会关注loop的问题，而如果我们将<>换成 < 或 > ，那么也不会报loop的错，因为这样的关系导致重复记录不可能出现

这估计就是“规定”。

另外我上面例子中说的“数据的特点”，指的是level<3，而不是具体的表里的记录

newkid

你现在的认识依然没有任何新东西。
而我现在能够用一种观察结果解释这些现象。它就是：
ORACLE会检查所有CONNECT BY里面出现的PRIOR列或者表达式，一旦这些值在“当前路径”出现过，就报错。
如果CONNECT BY里面没有PRIOR，就不会检查。

lastwinner

就“ORACLE会检查所有CONNECT BY里面出现的PRIOR列或者表达式，一旦这些值在“当前路径”出现过，就报错。
如果CONNECT BY里面没有PRIOR，就不会检查。”

而言，我也表达出了同样的意思，并认为这是Oracle的“规定”

newkid

以前的讨论都没有明确定义什么算“重复”。现在比较清楚了，每一个PRIOR列或者表达式构成的一组值被用于判断重复，所以没有PRIOR也就没有重复，不会报错。

prior name<>name会报错，再加上个 and prior sys_guid() is not null就不会，尽管后者完全没有体现前后级数据的关系，看似无厘头，但是因为此时检查的将是 (name, sys_guid) 这样一组值，永远不会出现重复。

lastwinner

newkid 发表于 2017-3-21 22:09
以前的讨论都没有明确定义什么算“重复”。现在比较清楚了，每一个PRIOR列或者表达式构成的一组值被用于判 ...

其实oracle应该将带有level<xx的connect by，都认为即便有重复也没啥，毕竟这不可能进入无限循环

newkid

lastwinner 发表于 2017-3-21 22:33
其实oracle应该将带有level

LEVEL<LEVEL+1 不就是死循环?

lastwinner

newkid 发表于 2017-3-21 22:43
LEVEL

类似于这样的死循环，也直接报错