--测试数据
create table t1(row int,childid varchar(10),parentid varchar(10))
insert t1 select 1,'a','b'
union all select 2,'a','c'
union all select 3,'d','a'
union all select 4,'d','b'
union all select 5,'c','b'
union all select 6,'e','d'
union all select 7,'f','e'
create table t2(row int,leafid varchar(10),parentid varchar(10))
insert t2 select 1,'x1','e'
union all select 2,'x2','e'
union all select 3,'x1','f'
union all select 4,'x3','f'
go
--查询函数
create function f_id(
@id varchar(10) --要查询的非叶子节点
)returns @re table(id varchar(10),level int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.childid,@l
from t1 a,@re b
where a.parentid=b.id and b.level=@l-1
end
return
end
go
--调用函数实现查询
select a.*,b.*
from f_id('a') ab
join t1 a on a.childid=ab.id
left join t2 b on b.parentid=ab.id --如果只显示匹配的,用join
go
--删除测试
drop table t1,t2
drop function f_id
/*--测试结果
row childid parentid row leafid parentid
----------- ---------- ---------- ----------- ---------- ----------
1 a b NULL NULL NULL
2 a c NULL NULL NULL
3 d a NULL NULL NULL
4 d b NULL NULL NULL
6 e d 1 x1 e
6 e d 2 x2 e
7 f e 3 x1 f
7 f e 4 x3 f
看错了,没看见一段话,这个可以用一个函数来做:
假设表结构为:id varchar(10),parentid varchar(10) (编号,父级编号)
create function getchildren(@id varchar(10))
returns @r table(id varchar(10),level int)
as
begin
declare @i int
set @i=1
insert into @r select id,@i from [table] where parentid=@id
while @@rowcount>0
begin
set @i=@i+1
insert into @r select id,@i from [table] a join @r b on a.parentid=b.id and b.level=@i-1
end
return
end