代码
--创建表
create table Dept(ID int,ParentID int,Name varchar(20));
--增加测试数据,和上面的SqlServer数据相同
insert into Dept select 1,0,'AA' from dual;
insert into Dept select 2,1,'BB' from dual;
insert into Dept select 3,1,'CC' from dual;
insert into Dept select 4,2,'DD' from dual;
insert into Dept select 5,3,'EE' from dual;
insert into Dept select 6,0,'FF' from dual;
insert into Dept select 7,6,'GG' from dual;
insert into Dept select 8,7,'HH' from dual;
insert into Dept select 9,7,'II' from dual;
insert into Dept select 10,7,'JJ' from dual;
insert into Dept select 11,9,'KK' from dual;
commit;
--查询根节点(父节点)
select * from Dept --查询基础表
connect by id=prior parentid --connect by就是字段的关联关键字,prior有预先和前的意思,则是放在哪个字段前,哪个就是递归的上一层
start with name='II'; --start with则是递归的起始位置,也可以用id或者是parentid。可以修改II的值测试其他数据。
--查询结果
ID PARENTID NAME
7 II
6 GG
0 FF
--查询子节点
select * from Dept
connect by prior id=parentid --同样的语句,仅仅改变prior位子,就发生了指向性的变化,就是这里id为递归上一层。
start with name='II';
--查询结果
ID PARENTID NAME
7 II
9 KK
--测试结果和SqlServer一致,语句却更精练,简洁易懂。
复制代码