--处理的存储过程
create proc p_process
@节点号 int
as
create table #t(id int,level int)
declare @l int
set @l=0
insert #t select 节点号,@l from 树表 where 父节点号=@节点号
while @@rowcount>0
begin
set @l=@l+1
insert #t select a.节点号,@l
from 树表 a join #t b on a.父节点号=b.id and b.level=@l-1
end
select a.节点号,a.节点名,a.父节点号
from 树表 a join #t b on a.节点号=b.id
go--调用
exec p_process 1
create proc p_process
@节点号 int
as
create table #t(id int,level int)
declare @l int
set @l=0
insert #t select 节点号,@l from 树表 where 父节点号=@节点号
while @@rowcount>0
begin
set @l=@l+1
insert #t select a.节点号,@l
from 树表 a join #t b on a.父节点号=b.id and b.level=@l-1
end
select a.节点号,a.节点名,a.父节点号
from 树表 a join #t b on a.节点号=b.id
go--调用
exec p_process 1
declare @a table (TC_Id int,TC_PID int,TC_Name varchar(200))
insert @a values(1,0,'中国')
insert @a values(2,0,'美国')
insert @a values(3,0,'加拿大')
insert @a values(4,1,'北京')
insert @a values(5,1,'上海')
insert @a values(6,1,'江苏')
insert @a values(7,6,'苏州')
insert @a values(8,7,'常熟')
insert @a values(9,6,'南京')
insert @a values(10,6,'无锡')
insert @a values(11,2,'纽约')
insert @a values(12,2,'旧金山')declare @tmp1 table (TC_Id int,TC_PID int,TC_Name varchar(200),lev int)
insert @tmp1 select *,1 from @a where tc_ID=1
while @@rowcount>0
insert @tmp1 select a.*,1 from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1)
select * from @tmp1/*--按子找父--*/
--建立环境
declare @a table (TC_Id int,TC_PID int,TC_Name varchar(200))
insert @a values(1,0,'中国')
insert @a values(2,0,'美国')
insert @a values(3,0,'加拿大')
insert @a values(4,1,'北京')
insert @a values(5,1,'上海')
insert @a values(6,1,'江苏')
insert @a values(7,6,'苏州')
insert @a values(8,7,'常熟')
insert @a values(9,6,'南京')
insert @a values(10,6,'无锡')
insert @a values(11,2,'纽约')
insert @a values(12,2,'旧金山')declare @tmp1 table (TC_Id int,TC_PID int,TC_Name varchar(200))--开始结点
insert @tmp1 select * from @a where tc_ID=10--循环得到
while @@rowcount>0
insert @tmp1 select a.* from @a a,@tmp1 b where a.tc_id=b.tc_pID and a.tc_ID not in (select tc_ID from @tmp1)
create function gettree(@nodeid int)
returns @r table(nodeid int)
as
begin
declare @t table(level int,nodeid int)
declare @i int
set @i=0
insert into @t select @i,@nodeid
while @@rowcount>0
begin
insert into @r select nodeid from @t where level=@i
set @i=@i+1
insert into @t(nodeid,level) select 节点号,@i from [table] a join @t b on a.父节点号=b.nodeid and b.level=@i-1
end
end
go
select * from [table] where 节点号 not in (select nodeid from dbo.gettree(1))