sql树型应用总结

--测试数据
if object_id('[T]') is not null drop table [T]
create table [T] (ID int,PID int,NAME varchar(10))
insert into [T]
select 1,0,'A' union all
select 2,1,'B' union all
select 3,2,'C' union all
select 4,0,'D' union all
select 5,4,'E' union all
select 6,2,'F' union all
select 7,3,'G' union all
select 8,7,'H'
go

--获取所有的要根结点
select t.* from T t where  exists (select 1 from T s where s.pid = t.id) 
go

--获取所有的叶子结点
select t.* from T t where not exists (select 1 from T s where s.pid = t.id) 
go

--获取树型记录的全路径

if object_id('[dbo].[getALLPath]') is not null
drop function [dbo].[getALLPath]
go
create  function getALLPath()
returns   @t   table(id   int,pid   int,name varchar(50),path varchar(300))
as
begin
 insert into @t select id,pid,name,null as path from T
 update @t  set path=name
 DECLARE @i int,@j int
 set @i=0
 set @j=1
 select @i=max(pid) from @t
 while @j<=@i
 begin
     update v set  path=a.path+'.'+v.name
     from @t v inner join @t a on v.pid=a.id
     where v.pid=@j
     set @j=@j+1
 end
return
end
go

select * from getALLPath()
go

--获取树型记录的全路径
if object_id('[dbo].[getpath]') is not null
drop function [dbo].[getpath]
go
create  function getpath(@id varchar(20))
returns varchar(300)
as
begin
 declare @s varchar(300)
 select @s = name,@id = pid  from T where id = @id
 while exists (select 1 from T where id = @id )
   select @s = name+'.'+@s,@id = pid from T where id = @id
 return @s
end
go

select t.*,dbo.getpath(cast(id as varchar(20))) path from T
go

--查找树
if object_id('[dbo].[GetChild]') is not null
drop function [dbo].[GetChild]
go
create    function   GetChild(@id   int)  
returns   @returnT   table(pid   int,id   int,name varchar(50))  
AS  
begin  
  insert   into   @returnT   select   pid,id,name  from   T   where   id=@id 
  insert   into   @returnT   select   pid,id,name  from   T   where   pid=@id    
    while   @@rowcount>0  
      insert   into   @returnT   select   A.pid,A.id,A.name
      from   T   A   inner   join   @returnT   B   on   A.pid=B.id  
      where   A.id   not   in(select   id   from   @returnT)
   return 
end  
go

select * from GetChild(1)
go

--查找树型记录的全路径
if object_id('[dbo].[getFindPath]') is not null
drop function [dbo].[getFindPath]
go
create  function getFindPath(@parentId varchar(20))
returns   @returndepartment   table(id   int,pid   int,name varchar(50),path varchar(300))
as
begin
 declare @t   table(id   int,pid   int,name varchar(50),path varchar(300))
 insert into @t select id,pid,name,null as path from T
 update @t  set path=name
 DECLARE @i int,@j int
 set @i=0
 set @j=1
 select @i=max(pid) from @t
 while @j<=@i
 begin
     update v set  path=a.path+'.'+v.name
     from @t v inner join @t a on v.pid=a.id
     where v.pid=@j
     set @j=@j+1
 end
 insert   into   @returndepartment   select   *   from   @t   where id=@parentId
 insert   into   @returndepartment   select   *   from   @t   where pid=@parentId    
 while   @@rowcount>0  
     insert   into   @returndepartment   select   a.id,a.pid,a.name,a.path
     from   @t   A   inner   join   @returndepartment   B   on   A.pid=B.id 
     where   A.id   not   in(select   id   from   @returndepartment)    
return
end
go

select * from getFindPath(1)
go