SQL 层次查询

语法：


[Java] view plain copy


 print?
SELECT [LEVEL],column,expr...  
FROM table  
[WHERE condition(s)]  
[START WITH condition(s)]  
[CONNECT BY PRIOR condition(s)];  


 
总结：
1.其中level关键字是可选的，level是层次查询的一个伪列，如果有level，必须有connect by,start with可以没有，level不可以前面加表名。
2.From之后可以是table,view但是只能是一个table。
3.Where条件限制了查询返回的行，但是不影响层次关系，不满足条件的节点不返回。
4.Start with是表示开始节点，如果没有start with,则每行都当作root。Start with后面可以使用子查询，可以带多个条件。
5.Connect by与prior 确定一个层次查询的条件和遍历的方向(prior 确定)。Connect by prior column_1=column_2;(其中prior表示前一个节点的意思)。就是先由根节点，然后遍历子节点。column_1表示父key,column_2表示子key。即这种情况下：connect by prior 父key=子key表示自顶向下，等同于connect by 子key=prior 父key.
注意：start with 和connect by prior不是ANSI标准sql。
where子句会将满足条件的节点删除，但是被删除节点的后代不会受到影响。
connect by 中加上条件会将满足条件的整个树枝包括后代都删除。
 
测试数据：
 
[java] view plain copy


 print?
drop table T;  
create table T  
(  
  ID   NUMBER,  
  PID  NUMBER,  
  NAME VARCHAR2(50),  
  SAL  NUMBER  
);  
insert into T values(1,null,'春哥',800);  
insert into T values(2,1,'曾哥',1200);  
insert into T values(3,1,'王五',1000);  
insert into T values(4,2,'小明',1800);  
insert into T values(5,2,'小红',2000);  
insert into T values(6,3,'翠花',3000);  
insert into T values(7,5,'小昭',1400);  
insert into T values(8,5,'春兰',1800);  
insert into T values(9,5,'秋菊',2800);  
insert into T values(10,8,'缝小肛',1600);  
commit;  


 


[java] view plain copy


 print?
select * from T;  
        ID        PID NAME                                                      SAL  
---------- ---------- -------------------------------------------------- ----------  
         1            春哥                                                      800  
         2          1 曾哥                                                     1200  
         3          1 王五                                                     1000  
         4          2 小明                                                     1800  
         5          2 小红                                                     2000  
         6          3 翠花                                                     3000  
         7          5 小昭                                                     1400  
         8          5 春兰                                                     1800  
         9          5 秋菊                                                     2800  
        10          8 缝小肛                                                   1600  


 
例子：


[java] view plain copy


 print?
select level,t.name,t.sal from t  
start with t.id=1  
connect by prior t.id=t.pid  
order by level;  
  
     LEVEL NAME                                                      SAL  
---------- -------------------------------------------------- ----------  
         1 春哥                                                      800  
         2 曾哥                                                     1200  
         2 王五                                                     1000  
         3 小明                                                     1800  
         3 小红                                                     2000  
         3 翠花                                                     3000  
         4 秋菊                                                     2800  
         4 春兰                                                     1800  
         4 小昭                                                     1400  
         5 缝小肛                                                   1600  
  
select level,t.name,t.sal from t  
where t.sal > 1500  
start with t.pid is null  
connect by prior t.id=t.pid;  
  
     LEVEL NAME                                                      SAL  
---------- -------------------------------------------------- ----------  
         3 小明                                                     1800  
         3 小红                                                     2000  
         4 春兰                                                     1800  
         5 缝小肛                                                   1600  
         4 秋菊                                                     2800  
         3 翠花                                                     3000  
    


 
 层次查询限制：
1.层次查询from 之后如果是table，只能是一个table，不能有join。
2.from之后如果是view，则view不能是带join的。
3.使用order by子句，order 子句是在等级层次做完之后开始的，所以对于层次查询来说没有什么意义，除非特别关注level，获得某行在层次中的深度，但是这两种都会破坏层次。
4.在start with中表达式可以有子查询，但是connect by中不能有子查询。
以上是10g之前的限制，10g之后可以使用带join的表和视图，connect by中可以使用子查询。
 


[java] view plain copy


 print?
--查看总共有几个等级：  
select count(distinct level) from t  
start with t.id=1  
connect by prior t.id=t.pid;  
  
COUNT(DISTINCTLEVEL)  
--------------------  
                   5  
  
--查看每个等级上有多少个节点:  
select level,count(*) from t  
start with t.id=1  
connect by prior t.id=t.pid  
group by level;  
  
     LEVEL   COUNT(*)  
---------- ----------  
         1          1  
         2          2  
         4          3  
         5          1  
         3          3  


 


[java] view plain copy


 print?
/* 
Oracle 9i提供了sys_connect_by_path(column,char),其中column 是字符型或能自动转换成字符型的列名。它的主要目的就是将父节点到当前节点的”path”按照指定的模式展现出现。这个函数只能使用在层次查询中。 
*/  
select level,sys_connect_by_path(t.name, '=>') from t  
start with t.pid is null  
connect by prior t.id=t.pid;  
  
     LEVEL SYS_CONNECT_BY_PATH(T.NAME,'=>  
---------- --------------------------------------------------------------------------------  
         1 =>春哥  
         2 =>春哥=>曾哥  
         3 =>春哥=>曾哥=>小明  
         3 =>春哥=>曾哥=>小红  
         4 =>春哥=>曾哥=>小红=>小昭  
         4 =>春哥=>曾哥=>小红=>春兰  
         5 =>春哥=>曾哥=>小红=>春兰=>缝小肛  
         4 =>春哥=>曾哥=>小红=>秋菊  
         2 =>春哥=>王五  
         3 =>春哥=>王五=>翠花  


 
 
[java] view plain copy


 print?
/* 
前面说了，对于层次查询如果用order by排序，比如order by last_name则是先做完层次获得level,然后按last_name 排序，这样破坏了层次，比如特别关注某行的深度，按level排序，也是会破坏层次的。在oracle10g中，增加了siblings 关键字的排序。语法：order siblings by <expre> 它会保护层次，并且在每个等级中按expre排序。 
*/  
  
select level,t.* from t     
start with t.id=1    
connect by prior t.id=t.pid     
order siblings by t.name;  
  
     LEVEL         ID        PID NAME                                                      SAL  
---------- ---------- ---------- -------------------------------------------------- ----------  
         1          1            春哥                                                      800  
         2          3          1 王五                                                     1000  
         3          6          3 翠花                                                     3000  
         2          2          1 曾哥                                                     1200  
         3          5          2 小红                                                     2000  
         4          8          5 春兰                                                     1800  
         5         10          8 缝小肛                                                   1600  
         4          9          5 秋菊                                                     2800  
         4          7          5 小昭                                                     1400  
         3          4          2 小明