使用Connect by进行递归查询
首先和使用WITH做递归查询一样,给数据库表插入一定的数据。
CREATE TABLE BBS ( PARENTID INTEGER NOT NULL, ID INTEGER NOT NULL, NAME VARCHAR(200) NOT NULL );insert into bbs (PARENTID,ID,NAME) values (0,1,'数据库开发');insert into bbs (PARENTID,ID,NAME) values (1,11,'DB2');insert into bbs (PARENTID,ID,NAME) values (11,111,'DB2 文章1');insert into bbs (PARENTID,ID,NAME) values (111,1111,'DB2 文章1 的评论1');insert into bbs (PARENTID,ID,NAME) values (111,1112,'DB2 文章1 的评论2');insert into bbs (PARENTID,ID,NAME) values (11,112,'DB2 文章2');insert into bbs (PARENTID,ID,NAME) values (1,12,'Oracle');insert into bbs (PARENTID,ID,NAME) values (0,2,'Java 技术');COMMIT;select * from BBS start with name = ‘DB2文章1的评论2’ --从某一个节点开始,进行向上递归connect by prior PARENTID = ID; --连接条件
INSERT INTO BBS(PARENTID,ID,NAME)VALUES(111,1,’DB2论文’);select * from BBS start with name = ‘DB2文章1的评论2’ --从某一个节点开始,进行向上递归connect by prior PARENTID = ID; --连接条件NOCYCLE
select a.child, a.PARENTID, level "层次", sys_connect_by_path(ID, '->') "合并层次", prior a.PARENTID "父节点", connect_by_root a.ID "根节点", decode(connect_by_isleaf, 1, a.ID, null) "子节点", decode(connect_by_isleaf, 1, '是', '否') "是否子节点"from BBS astart with name = ‘DB2文章1的评论2’ --从某一个节点开始,进行向上递归connect by NOCYCLE prior PARENTID = ID; --连接条件