sqlserver子父查询

发布时间：2020-12-12 14:02:53 所属栏目：MsSql教程来源：网络整理

导读：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('GetC

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!