数据库 SQL 遍历父子关系表（二叉树）获得所有子节点所有父节点

最新推荐文章于 2023-06-14 16:23:22 发布

最新推荐文章于 2023-06-14 16:23:22 发布 · 450 阅读

文章标签：

#数据库 #数据结构与算法

本文介绍如何使用SQL通过递归查询方式获取二叉树结构中指定节点的所有子节点及父节点。通过定义存储过程并利用While循环不断查找直接子节点或父节点，直至没有更多节点可查。适用于需要遍历复杂层级关系的场景。

数据库 SQL 遍历父子关系表（二叉树）获得所有子节点所有父节点

创建表
Create Table A
(IDInt,
fatherIDInt,
NameVarchar(10)
)
Insert A Select 1, NULL, 'tt'
Union All Select 2, 1, 'aa'
Union All Select 3, 1, 'bb'
Union All Select 4, 2, 'cc'
Union All Select 5, 2, 'gg'
Union All Select 6, 4, 'yy'
Union All Select 7, 4, 'jj'
Union All Select 8, 7, 'll'
Union All Select 9, NULL, 'uu'
Union All Select 10, 9, 'oo'
GO
创建函数

//取字子节点
Create Function GetChildren(@ID Int)
Returns @Tree Table (ID Int, fatherID Int, Name Varchar(10))
As
Begin
Insert @Tree Select ID, fatherID, Name From A Where fatherID = @ID
While @@Rowcount > 0
Insert @Tree Select A.ID, A.fatherID, A.Name From A A Inner Join @Tree B On A.fatherID = B.ID And A.ID Not In (Select ID From @Tree)
Return
End
GO

//取父节点

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[GetParent](@ID Int)
Returns @Tree Table (ID Int, fatherID Int, NameVar Varchar(10))
As
Begin
Insert @Tree Select ID, fatherID, NameVar From A Where ID = @ID
While @@Rowcount > 0
Insert @Tree Select A.ID, A.fatherID, A.NameVar From A A Inner Join @Tree B On A.ID = B.fatherID And A.ID Not In (Select ID From @Tree)
Return
End

--測試
Select * From dbo.GetChildren(1)

Select * From dbo.GetParent(9)
GO