这是一个典型的递归问题，参考FAQ中以下三个帖子：http://community.csdn.net/Expert/FAQ/FAQ_Index.asp?id=92690http://community.csdn.net/Expert/FAQ/FAQ_Index.asp?id=47256http://community.csdn.net/Expert/FAQ/FAQ_Index.asp?id=70519
调试欢乐多
create table infotype(id int,parentid int,content varchar(10))
insert infotype select 30,1 ,'name1'
union all select 31,30,'name2'
union all select 32,31,'name3'
union all select 33,32,'name4'
union all select 34,2 ,'name5'create table info(id int,subject varchar(10),infotype int)
insert info select 1,'subject1',33
union all select 2,'subject2',32
union all select 3,'subject3',34
union all select 4,'subject4',34
go--查询指定id的所有子
create function f_cid(
@id int
)returns @re table([id] int,[level] int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.[id],@l
from [infotype] a,@re b
where a.[parentid]=b.[id] and b.[level]=@l-1
end
return
end
go--调用实现查询
select a.*,b.*
from infotype a,info b,f_cid(1) c --查1类下的所有信息
where a.id=b.infotype and b.infotype=c.id
go--删除测试
drop table infotype,info
drop function f_cid/*--测试结果id parentid content id subject infotype
----------- ----------- ---------- ----------- ---------- -----------
32 31 name3 2 subject2 32
33 32 name4 1 subject1 33（所影响的行数为 2 行）
--*/