sqlserver子父查询

最新推荐文章于 2024-01-15 15:31:09 发布

转载最新推荐文章于 2024-01-15 15:31:09 发布 · 865 阅读

sqlserver 专栏收录该内容

8 篇文章

订阅专栏

本文介绍使用SQL通过自定义函数及公用表表达式两种方法实现父子节点的查询，包括父节点查询子节点及子节点查询父节点的具体实现。

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