1 create table tb(id varchar(3) , pid varchar(3) , name varchar(10)) 2 insert into tb values('001' , null , '广东省') 3 insert into tb values('002' , '001' , '广州市') 4 insert into tb values('003' , '001' , '深圳市') 5 insert into tb values('004' , '002' , '天河区') 6 insert into tb values('005' , '003' , '罗湖区') 7 insert into tb values('006' , '003' , '福田区') 8 insert into tb values('007' , '003' , '宝安区') 9 insert into tb values('008' , '007' , '西乡镇') 10 insert into tb values('009' , '007' , '龙华镇') 11 insert into tb values('010' , '007' , '松岗镇') 12 go 13 14 --查询指定节点及其所有父节点的函数 15 create function f_pid(@id varchar(3)) returns @t_level table(id varchar(3)) 16 as 17 begin 18 insert into @t_level select @id 19 select @id= pid from tb where id =@id and pid is not null 20 while @@ROWCOUNT>0 21 begin 22 insert into @t_level select @id select @id= pid from tb where id =@id and pid is not null 23 end 24 return 25 end 26 go 27 28 --调用函数查询002(广州市)及其所有父节点 29 select a.*from tb a , f_pid('002') b where a.id = b.id order by a.id
执行结果
1 /* 2 id pid name 3 ---- ---- ---------- 4 001 NULL 广东省 5 002 001 广州市 6 */
