SQL语句遍历树结构的数据表

[转自]：肆尾葱的博客《SQL语句遍历树结构的数据表》

一、创建树结构的数据库表

-- 数据库为 SQL Server
create table treeTest(
    id int,
    parentId int,
    info varchar(10)
)

-- 插入数据
insert into treeTest(id,parentId,info) values(1,null,'root');
insert into treeTest(id,parentId,info) values(2,1,'two');
insert into treeTest(id,parentId,info) values(3,1,'three');
insert into treeTest(id,parentId,info) values(7,2,'seven');
insert into treeTest(id,parentId,info) values(8,2,'eight');
insert into treeTest(id,parentId,info) values(9,2,'nine');
insert into treeTest(id,parentId,info) values(4,3,'four');
insert into treeTest(id,parentId,info) values(5,3,'five');
insert into treeTest(id,parentId,info) values(6,3,'six');
insert into treeTest(id,parentId,info) values(14,7,'fourteen');
insert into treeTest(id,parentId,info) values(15,7,'fifteen');
insert into treeTest(id,parentId,info) values(16,8,'sixteen');
insert into treeTest(id,parentId,info) values(17,9,'seventeen');
insert into treeTest(id,parentId,info) values(10,4,'ten');
insert into treeTest(id,parentId,info) values(11,5,'eleven');
insert into treeTest(id,parentId,info) values(12,5,'twelve');
insert into treeTest(id,parentId,info) values(13,6,'thirteen');

二、通用的 SQL 遍历方法

在 SQL 语句中使用 with as 语句，详细介绍参考：
SQL中使用WITH AS提高性能－使用公用表表达式（CTE）简化嵌套SQL

-- 遍历节点id为3的所有子节点
-- 小括号中的参数需与查询表中的字段个数一致，即两个 select 关键字后的字段个数，有些数据库不允许第2个select关键字后用*号表示所有字段，如oracle
-- temp.id = t.parentId 没有顺序要求，如果改为 temp.parentId = t.id 则语句向上遍历所有祖先节点
with temp(id,parentId,info)
as
(
    select * from treeTest
    where id = 3
    union all
    select t.* from treeTest t,temp
    where temp.id = t.parentId
) select * from temp

返回结果：

三、Oracle 特有的树遍历方法

-- 从id=5的节点开始，向下遍历所有子孙节点
-- prior 关键字可放在 treeTest.id 或 treeTest.parentId 的前面，决定遍历的方向
select * from treeTest 
start with treeTest.id=5 
connect by prior treeTest.id = treeTest.parentId