SQL问题 实现递归查询

下面是我做过的题目.你是哪个表,在换一下名称 create proc p_recursion @v_uid varchar(5)
as
begin
--创建一个临时表用于存储结果
create table #TBuidres(ID varchar(5), UID varchar(5))
--插入初始条件
insert #TBuidres(ID, UID)
select ID, UID from TBuid where ID = @v_uid

declare @v_temp varchar(5)
set @v_temp = isnull(@v_temp,'')

--为临时表打开一个游标
declare vp_i cursor for select ID from #TBuidres
open vp_i
fetch next from vp_i into @v_temp

--根据临时表中ID去寻找TBuid中对应UID的记录插入临时表
while @@FETCH_STATUS = 0
begin
insert #TBuidres(ID,UID)
select ID,UID from TBuid where UID = @v_temp

fetch next from vp_i into @v_temp
end

--返回结果
select ID,UID from #TBuidres

--处理游标后事
close vp_i
deallocate vp_i
end

--存储过程到此结束---------------------------------------------------------------------------------
--运行
exec p_recursion '001'

--sql 2005 递归--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] int,[col1] varchar(8),[col2] int)
insert [tb]
select 1,'河北省',0 union all
select 2,'邢台市',1 union all
select 3,'石家庄市',1 union all
select 4,'张家口市',1 union all
select 5,'南宫',2 union all
select 6,'坝上',4 union all
select 7,'任县',2 union all
select 8,'清河',2 union all
select 9,'河南省',0 union all
select 10,'新乡市',9 union all
select 11,'aaa',10 union all
select 12,'bbb',10;with t as(
select * from [tb] where col1='河北省'
union all
select a.* from [tb] a ,t where a.col2=t.id)
select * from t
/*
id col1 col2
----------- -------- -----------
1 河北省 0
2 邢台市 1
3 石家庄市 1
4 张家口市 1
6 坝上 4
5 南宫 2
7 任县 2
8 清河 2(8 行受影响)
*/