SqlServer父节点与子节点查询及递归

最新推荐文章于 2024-02-29 14:21:51 发布

s115859272

最新推荐文章于 2024-02-29 14:21:51 发布

阅读量305

点赞数

分类专栏：数据库操作文章标签： SQL Server .net

数据库操作专栏收录该内容

8 篇文章

订阅专栏

本文介绍了在SQLServer中实现递归查询的方法，包括使用用户自定义函数和公用表表达式来查询父节点的所有子节点及子节点的父节点。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

转载地址： http://blog.youkuaiyun.com/softkexin/article/details/7389443

前言:

在实际开发中,我们经常需要根据一个父节点获取所有的子节点,也就是所谓的递归.有的时候也需要根据子节点,获取它的父节点.

下面我介绍两种方式实现上面的需求:

用户自定义函数方式(Sql Server 2000+)
公用表表达式方式(Sql Server 2005+)
Hierarchyid方式(Sql Server 2008+)--待研究

实现:

/*
标题:sql server中递归的实现
作者:axin
时间:2012-3-24
*/
set nocount on
if OBJECT_ID('tb','U') is not null drop table tb
go
create table tb(ID int,PID INT)
insert into tb
select 1,0 union all
select 2,1 union all
select 3,2 union all
select 4,3 union ALL
select 5,4 union ALL
select 6,5 union ALL
select 7,6 
--自定义函数方式实现父节点查询子节点
if OBJECT_ID('GetChildID') is not null drop function GetChildID
go
create function GetChildID(@ParentID int)
returns @t table(ID int)
as
  begin
     insert into @t select ID from tb where PID=@ParentID
     while @@rowcount<>0
     begin
        insert into @t select a.ID from tb as a 
        inner join @t as b
        on a.PID=b.ID
        and not exists(select 1 from @t where ID=a.ID)
     end
     return 
  end
go
select * from dbo.GetChildID(1)
--自定义函数方式实现子节点查询父节点
if OBJECT_ID('GetParentID') is not null drop function GetParentID
go
create function GetParentID(@ChildID int)
returns @t table(PID int)
as
  begin
     insert into @t select PID from tb where ID=@ChildID
     while @@rowcount<>0
     begin
        insert into @t select a.PID from tb as a 
        inner join @t as b
        on a.ID=b.PID
        and not exists(select 1 from @t where PID=a.PID)
     end
     return 
  end
go
select * from dbo.GetParentID(3)
--公用表表达式实现父节点查询子节点(SqlServer2005+)
DECLARE @ParentID int
SET @ParentID=1
with CTEGetChild as
(
select * from tb where PID=@ParentID
UNION ALL
 (SELECT a.* from tb as a inner join
  CTEGetChild as b on a.PID=b.ID
 )
)
SELECT * FROM CTEGetChild
--公用表表达式实现子节点查询父节点(SqlServer2005+)
DECLARE @ChildID int
SET @ChildID=6
DECLARE @CETParentID int
select @CETParentID=PID FROM tb where ID=@ChildID
with CTEGetParent as
(
select * from tb where ID=@CETParentID
UNION ALL
 (SELECT a.* from tb as a inner join
  CTEGetParent as b on a.ID=b.PID
 )
)
SELECT * FROM CTEGetParent