--测试数据
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<[email protected]
begin
update v set path=a.path+'.'+v.name
from @t v inner join @t a on v.pid=a.id
where [email protected]
set @[email protected]+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 = [email protected],@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 [email protected]
insert into @returnT select pid,id,name from T where [email protected]
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<[email protected]
begin
update v set path=a.path+'.'+v.name
from @t v inner join @t a on v.pid=a.id
where [email protected]
set @[email protected]+1
end
insert into @returndepartment select * from @t where [email protected]
insert into @returndepartment select * from @t where [email protected]
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