2023年6月27日发(作者：)

SQL语句遍历树结构的数据表[转⾃]：肆尾葱的博客⼀、创建树结构的数据库表-- 数据库为 SQL Servercreate 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 语句，详细介绍参考：

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

三、Oracle 特有的树遍历⽅法-- 从id=5的节点开始，向下遍历所有⼦孙节点-- prior 关键字可放在 或 Id 的前⾯，决定遍历的⽅向select * from treeTest

start with =5

connect by prior = Id