日期:2014-05-16 浏览次数:20846 次
create table tb(region_id int, parent_id int, region_name varchar(10)) insert into tb values(1 , 0 , '广东') insert into tb values(2 , 1 , '广州') insert into tb values(3 , 1 , '佛山') insert into tb values(4 , 2 , '海珠区') go --查询指定节点及其所有子节点的函数 create function f_cid(@region_id int) returns @t_level table(region_id int , level int) as begin declare @level int set @level = 1 insert into @t_level select @region_id , @level while @@ROWCOUNT > 0 begin set @level = @level + 1 insert into @t_level select a.region_id , @level from tb a , @t_Level b where a.parent_id = b.region_id and b.level = @level - 1 end return end go --调用函数查询1(广东)及其所有子节点 select a.* from tb a , f_cid(1) b where a.region_id = b.region_id order by a.region_id /* region_id parent_id region_name ----------- ----------- ----------- 1 0 广东 2 1 广州 3 1 佛山 4 2 海珠区 (所影响的行数为 4 行) */ --调用函数查询2(广州)及其所有子节点 select a.* from tb a , f_cid(2) b where a.region_id = b.region_id order by a.region_id /* region_id parent_id region_name ----------- ----------- ----------- 2 1 广州 4 2 海珠区 (所影响的行数为 2 行) */ drop table tb drop function f_cid