MSSQL 查找子結節與父節點

-->Title:Generating test data

-->Author:wufeng4552

-->Date :2009-09-30 08:52:38

set nocount on

if object_id('tb','U')is not null drop table tb

go

create table tb(ID int, ParentID int)

insert into tb select 1,0

insert into tb select 2,1

insert into tb select 3,1

insert into tb select 4,2

insert into tb select 5,3

insert into tb select 6,5

insert into tb select 7,6

-->Title:查找指定節點下的子結點

if object_id('Uf_GetChildID')is not null drop function Uf_GetChildID

go

create function Uf_GetChildID(@ParentID int)

returns @t table(ID int)

as

begin

insert @t select ID from tb where ParentID=@ParentID

while @@rowcount<>0

begin

insert @t select a.ID from tb a inner join @t b

on a.ParentID=b.id and

not exists(select 1 from @t where id=a.id)

end

return

end

go

select * from dbo.Uf_GetChildID(5)

/*

ID

-----------

6

7

*/

-->Title:查找指定節點的所有父結點

if object_id('Uf_GetParentID')is not null drop function Uf_GetParentID

go

create function Uf_GetParentID(@ID int)

returns @t table(ParentID int)

as

begin

insert @t select ParentID from tb where ID=@ID

while @@rowcount!=0

begin

insert @t select a.ParentID from tb a inner join @t b

on a.id=b.ParentID and

not exists(select 1 from @t where ParentID=a.ParentID)

end

return

end

go

select * from dbo.Uf_GetParentID(2)

/*

ParentID

-----------

1

0

*/