create table t_1(code varchar(10),parent varchar(10))
insert into t_1(code,parent)
select 'A','0'
union all select 'E','A'
union all select 'E01','E'
union all select 'E02','E'
create table t_2(code varchar(10),value int)
insert into t_2(code,value)
select 'E01',3
union all select 'E02',4
--递归取下级
create function f_getchild(@code varchar(10))
returns @t table(code varchar(10))
as
begin
declare @t_temp table(id int identity(1,1),child varchar(10))
insert into @t(code)
select code from t_1 where Parent = @code
insert into @t_temp(child)
select code from t_1 where Parent = @code
declare @child_temp varchar(10),@max_id int,@min_id int
select @max_id = max(id),@min_id = min(id) from @t_temp
while @min_id <= @max_id
begin
select @child_temp = child from @t_temp where id = @min_id
insert into @t(code)
select * from dbo.f_getchild(@child_temp)
select @min_id = @min_id + 1
end
return
end
--调用
select code from dbo.f_getchild('E01')