--测试数据
create table tb(编号 int,名称 varchar(10),隶属 int)
insert into tb
select 1,'aa',0
union all select 2,'bb',1
union all select 3,'c',1
union all select 4,'dd',2
union all select 5,'ee',3
union all select 6,'ww',2
union all select 7,'qq',4
union all select 8,'ff',4
go
--创建函数,得到指定编号及其下属列表
create function f_getcid(@编号 int)
returns @t table(id int,level int)
as
begin
declare @level int
set @level=1
insert @t select 编号,@level from tb where 编号=@编号
while @@rowcount>0
begin
set @level=@level+1
insert @t select a.编号,@level from tb a join @t b on a.隶属=b.ID
where b.level=@level-1
end
return
end
go
--得到结果
select a.* from tb a join dbo.f_getcid(2) b on a.编号=b.id
--创建函数,得到指定编号及其下属列表
create function f_getcid(@编号 int)
returns @t table(id int,level int)
as
begin
declare @level int
set @level=1
insert @t select 编号,@level from tb where 编号=@编号
while @@rowcount>0
begin
set @level=@level+1
insert @t select a.编号,@level from tb a join @t b on a.隶属=b.ID
where b.level=@level-1
end
return
end
go
--得到结果
select a.* from tb a join dbo.f_getcid(2) b on a.编号=b.id
--测试数据
declare @tb table(编号 int,名称 varchar(10),隶属 int)
insert into @tb
select 1,'aa',0
union all select 2,'bb',1
union all select 3,'c',1
union all select 4,'dd',2
union all select 5,'ee',3
union all select 6,'ww',2
union all select 7,'qq',4
union all select 8,'ff',4
--查询处理
declare @编号 varchar(100)
set @编号=2
declare @t table(id int,level int)
declare @level int
set @level=1
insert @t select 编号,@level from @tb where 编号=@编号
while @@rowcount>0
begin
set @level=@level+1
insert @t select a.编号,@level from @tb a join @t b on a.隶属=b.ID
where b.level=@level-1
end
--得到结果
select a.* from @tb a join @t b on a.编号=b.id
declare @t table(id int,level int)
declare @level int
set @level=1
insert @t select 编号,@level from 表 where 编号=@编号
while @@rowcount>0
begin
set @level=@level+1
insert @t select a.编号,@level from 表 a join @t b on a.隶属=b.ID
where b.level=@level-1
end
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