树型结构表中递归获得某一结点及其所有的子孙结点
--实例数据库
create table [bt_area]([id] int identity(1,1),[pid] int,name varchar(20))
insert [bt_area] select 0,'中国'
union all select 0,'美国'
union all select 0,'加拿大'
union all select 1,'北京'
union all select 1,'上海'
union all select 1,'山东'
union all select 6,'济宁'
union all select 7,'微山'
union all select 6,'济南'
union all select 6,'德州'
union all select 2,'纽约'
union all select 2,'旧金山'
go
--获得本身及其子孙的数据库函数
create function [dbo].[getSelfAndChildid](@pid int)
returns @re table(id int)
as
begin
insert into @re select id from bt_area where pid=@pid
while @@rowcount>0
begin
insert into @re select a.id
from bt_area a inner join @re b on a.pid=b.id
where a.id not in(select id from @re)
end
insert into @re values(@pid)
return
end
--执行数据库函数
select * from bt_area where id in (select * from getSelfAndChildid(1))
create table [bt_area]([id] int identity(1,1),[pid] int,name varchar(20))
insert [bt_area] select 0,'中国'
union all select 0,'美国'
union all select 0,'加拿大'
union all select 1,'北京'
union all select 1,'上海'
union all select 1,'山东'
union all select 6,'济宁'
union all select 7,'微山'
union all select 6,'济南'
union all select 6,'德州'
union all select 2,'纽约'
union all select 2,'旧金山'
go
--获得本身及其子孙的数据库函数
create function [dbo].[getSelfAndChildid](@pid int)
returns @re table(id int)
as
begin
insert into @re select id from bt_area where pid=@pid
while @@rowcount>0
begin
insert into @re select a.id
from bt_area a inner join @re b on a.pid=b.id
where a.id not in(select id from @re)
end
insert into @re values(@pid)
return
end
--执行数据库函数
select * from bt_area where id in (select * from getSelfAndChildid(1))