请教sql语句递归查询问题
我有一个表结构如下:
id     upperid
1           2
3           2
4           1
5           3

具体层次不知道,我想用递归sql语句把所有属于某个upperid的数据,包括它的子树,都读出去，请问应该子怎么写?            
比如说   upperid   =2
那么先找到1,3,然后再由1,3找到4,5

使用sql语句实现
不知道大家明白不
谢谢!

------解决方案--------------------


Create table t(id int,upperid int)
insert into t
select 1, 2
union all select 3, 2
union all select 4, 1
union all select 5, 3
select * from t
create function aa(@upperid int)
returns @t table (id int,upperid int,level int)
as
begin
declare @i int
set @i=1
insert into @t
select *,@i from t where upperid=@upperid
while @@rowcount> 0
begin
set @i=@i+1
insert into @t
select a.*,@i from t a left join @t b on a.upperid=b.id
where b.level=@i-1
end
return
end

select * from dbo.aa(1)

id upperid level
----------- ----------- -----------
4 1 1

（所影响的行数为 1 行）

select * from dbo.aa(2)

id upperid level
----------- ----------- -----------
1 2 1
3 2 1
4 1 2
5 3 2

（所影响的行数为 4 行）
------解决方案--------------------
----创建测试数据
if object_id( 'tbTest ') is not null
drop table tbTest
if object_id( 'spGetChildren ') is not null
drop proc spGetChildren
GO
create table tbTest(id int, upperid int)
insert tbTest
select 1, 2 union all
select 3, 2 union all
select 4, 1 union all
select 5, 3
GO
----创建存储过程
create proc spGetChildren @id int
as
declare @t table(id int)
insert @t select id from tbTest where upperid = @id
while @@rowcount > 0
insert @t select a.id from tbTest as a inner join @t as b
on a.upperid = b.id and a.id not in(select id from @t)
select * from @t
GO

----执行存储过程
declare @upperid int
set @upperid = 2
EXEC spGetChildren @upperid

----清除测试环境
drop proc spGetChildren
drop table tbTest

/*结果
id
-----------
1
3
4
5
*/