高级sql学习——层次查询(Hierarical Retrival)！！！

语法：

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 中加上条件会将满足条件的整个树枝包括后代都删除。

 

测试数据：

 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;

 

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

 

例子：

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中可以使用子查询。

 

--查看总共有几个等级： 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

 

/* 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 =>春哥=>王五=>翠花

 

 /* 前面说了，对于层次查询如果用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 小明 1800