SQL层次查询
语法：
[Java] view plain copy
print?
SELECT [LEVEL],
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 =>春哥=>曾哥
connect和join的区别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 ⼩明
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论