use testgoif object_id('Tempdb..#T') is not null drop table #Tcreate table #T(ID int,Name nvarchar(100),parentID int)insert #T select 1,'A',0insert #T select 2,'B',1insert #T select 3,'C',1insert #T select 4,'D',3insert #T select 5,'E',2gowith t2(ID,Name,parentID,Ord,Lev)as(select *,Ord=cast(right('0000'+rtrim(ID),4) as nvarchar(max)),Lev=cast(0 as int) from #T where ParentID=0union allselect b.*,Ord=a.Ord+cast(right('0000'+rtrim(b.ID),4) as nvarchar(max)),Lev=cast(a.Lev+1 as int)from #T b join t2 a on b.ParentID=a.ID)select 顯示=replicate(char(9),lev)+Name from T2 order by ord option(maxrecursion 0)--用空格替換制表符時用space(lev*2)/replicate(char(9),lev)
/*
顯示---------------------------------A B E C D(5 個資料列受到影響)
*/;with t2as(select *,Ord=cast(Name as nvarchar(max)),lev=0 from #T where ParentID=0union allselect b.*,Ord=cast(a.Ord+char(9)+b.Name as nvarchar(max)),a.lev+1 from #T b join t2 a on b.ParentID=a.ID)select [顯示]=Ordfrom T2order by ordoption(maxrecursion 0)/*顯示--------------------AA BA B EA CA C D(5 個資料列受到影響)*/--2005多次调用连接时:/*;with T(COl)--可指定列名也可不指定as(......),T2(Co)as(......)select * from T1 a join T2 b on a.Col=b.COl*/本文地址：http://www.45fan.com/a/question/100243.html