高分请教：该SQL语句如何写？

[chq_top]

有一表Tab（id,parentid,data）,id指本身编码，parentid指父层id，如果没有父层（即自身已是根层），parentid为Null；编写SQL语句，查

询出每一个根层下所有子层、孙子层...的数据。示例：
1,null,data1
3,1,data3
4,3,data4
5,1,data5
2,null,data2
6,2,data6
7,2,data7
8,7,data8
根据要求，查询出来的结果如下：
parentid，id，data
1 ，1 ，data1
1 , 3 , data3
1 , 4 , data4
1 , 5 , data5
2 ，2 ，data2
2 , 6 , data6
2 , 7 , data7
2 , 8 , data8
要求父id均显示其根层的id。高分求救！！！

[txlicenhe]

请参考
http://expert.csdn.net/Expert/topic/1343/1343007.xml?temp=.1328546

[nboys]

try:
CREATE proc sub_Folder
@ID int
as
declare @FolderID int,@FolderName varchar(50)
declare sub2 cursor scroll local for
select id,FolderName from FileFolder where parentid=@ID
open sub2
fetch next from sub2 into @FolderID,@FolderName
while @@fetch_status = 0
begin
print space(@@nestlevel)+cast(@FolderID as varchar(50))+space(1)+@FolderName
if exists(select id from FileFolder where parentid=@FolderID)
begin
if @@NESTLEVEL<32
exec sub_Folder @FolderID
end
fetch next from sub2 into @FolderID,@FolderName
end
close sub2
deallocate sub2
GO


执行: exec sub_Folder 1

[nboys]

try:

CREATE proc sub_Folder
@ID int
as
declare @FolderID int,@FolderName varchar(50)
declare sub2 cursor scroll local for
select id,FolderName from FileFolder where parentid=@ID
open sub2
fetch next from sub2 into @FolderID,@FolderName
while @@fetch_status = 0
begin
print space(@@nestlevel)+cast(@FolderID as varchar(50))+space(1)+@FolderName
if exists(select id from FileFolder where parentid=@FolderID)
begin
if @@NESTLEVEL<32
exec sub_Folder @FolderID
end
fetch next from sub2 into @FolderID,@FolderName
end
close sub2
deallocate sub2
GO


执行: exec sub_Folder 1

[hjb111]

见:http://expert.csdn.net/Expert/topic/1343/1343007.xml?temp=.1328546