SQL SERVER 2000 遍历父子关系数据的表(二叉树)获得所有子节点 所有父节点及节点层数函数
---SQL SERVER 2000 遍历父子关系數據表(二叉树)获得所有子节点 所有父节点及节点层数函数
---Geovin Du 涂聚文
--建立測試環境
Create Table GeovinDu
([ID] Int,
fatherID Int,
[Name] Varchar(10)
)
Insert A Select 1, 0, '中国'
Union All Select 2, 1, '广东'
Union All Select 3, 1, '北京'
Union All Select 4, 2, '深圳特区'
Union All Select 5, 2, '广州'
Union All Select 6, 4, '罗湖'
Union All Select 7, 4, '福田'
Union All Select 8, 7, '华强北'
Union All Select 9, 0, '美国'
Union All Select 10, 9, '华盛顿州'
GO
--建立函數
--取字子节点
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetChildren]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetChildren]
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 GeovinDu Where fatherID = @ID
While @@Rowcount > 0
Insert @Tree Select A.ID, A.fatherID, A.Name From GeovinDu 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
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetParent]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetParent]
GO
Create Function [dbo].[GetParent](@ID Int)
Returns @Tree Table (ID Int, fatherID Int, Name Varchar(10))
As
Begin
Insert @Tree Select ID, fatherID, Name From GeovinDu Where ID = @ID
While @@Rowcount > 0
Insert @Tree Select A.ID, A.fatherID, A.Name From GeovinDu A Inner Join @Tree B On A.ID = B.fatherID And A.ID Not In (Select ID From @Tree)
Return
End
---分为几级
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getlevel]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_getlevel]
GO
create function f_getlevel(@id INT) returns int
as
begin
declare @re_str as varchar(3)
set @re_str = ''
declare @level as int
set @level = 1
select @re_str = fatherID from GeovinDu where [ID] = @id
while exists (select 1 from GeovinDu where [ID] = @re_str)
begin
select @re_str = fatherID from GeovinDu where [ID] = @re_str
set @level = @level + 1
end
return @level
end
go
--測試
SELECT * FROM GeovinDu
GO
Select * From dbo.GetChildren(4)
Select * From dbo.GetParent(4)
GO
select * , dbo.f_getlevel([ID]) 'level' from A
drop function dbo.f_getlevel
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步