oracle层次化查询
查了一下,层次化查询有时也称connect by 查询,是oracle特有的特性,这类查询可以选取数据,并使用层次化的次序返回结果。 connect by 和start with 子句语法如下: SELECT [LEVEL],COLUMN, expression,... FROM table [WHERE where_clause] [[START WITH
查了一下,层次化查询有时也称connect by 查询,是oracle特有的特性,这类查询可以选取数据,并使用层次化的次序返回结果。
connect by 和start with 子句语法如下:
SELECT [LEVEL],COLUMN, expression,... FROM table [WHERE where_clause] [[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];
参数含义如下:
- level:表示一个伪列,代表树的第几层(可选)。
- from table:只能写一个table,从一个table从查询。
- where :对返回结果进行了限定
- start_condition :定义了层次化查询的起点,编写层次化查询时必须指定start with 子句
- prior_condition : 定义了父行和子行之间的关系(上篇文章里已有描述),编写层次化查询时必须指定connect by prior 子句
LEVEL:
level是存在于Oracle所执行的所有查询中的伪列,它是一个数值,可以指出节点在树中所在的层次,在层次化查询中,level值将起始的根节点作为层次1,如:
select t.* , level, lpad('',4*level - 1) || name from T_TEST_WORD t start with pid='-1' connect by prior id = pid
示例:
用where条件进行截取:
select t.* , level, lpad('',4*level - 1) || name from T_TEST_WORD t where name != '无锡' start with pid='-1' connect by prior id = pid
层次查询限制:
1.层次查询from 之后如果是table,只能是一个table,不能有join。
2.from之后如果是view,则view不能是带join的。
3.使用order by子句,order子句是在等级层次做完之后开始的,所以对于层次查询来说没有什么意义,除非特别关注level,获得某行在层次中的深度,但是这两种都会破坏层次。见增强特性中的使用siblings排序。
4.在start with中表达式可以有子查询,但是connect by中不能有子查询。
以下内容摘自:http://blog.csdn.net/nsj820/article/details/6299276
1、SYS_CONNECT_BY_PATH
Oracle 9i提供了sys_connect_by_path(column,char),其中column是字符型或能自动转换成字符型的列名。它的主要目的就是将父节点到当前节点的”path”按照指定的模式展现出现。这个函数只能使用在层次查询中。
下面的是oracle10g新增特性
2、 CONNECT_BY_ISLEAF
在oracle9i的时候,查找指定root下的叶子节点,是很复杂的,oracle10g引入了一个新的函数,connect_by_isleaf,如果行的值为0表示不是叶子节点,1表示是叶子节点。
3、CONNECT_BY_ISCYCLE和NOCYCLE关键字
如果从root节点开始找其子孙,找到一行,结果发生和祖先互为子孙的情况,则发生循环,oracle会报ORA-01436: CONNECT BY loop in user data,在9i中只能将发生死循环的不加入到树中或删除,在10g中可以用nocycle关键字加在connect by之后,避免循环的参加查询操作。并且通过connect_by_iscycle得到哪个节点发生循环。0表示未发生循环,1表示发生了循环。
4、CONNECT_BY_ROOT
Oracle10g新增connect_by_root,用在列名之前表示此行的根节点的相同列名的值。
5、使用SIBLINGS关键字排序
对于层次查询如果用order by排序,比如order by last_name则是先做完层次获得level,然后按last_name排序,这样破坏了层次,比如特别关注某行的深度,按level排序,也是会破坏层次的。
在oracle10g中,增加了siblings关键字的排序。
语法:order siblings by
它会保护层次,并且在每个等级中按expre排序。