SQL2005的CTE

usetest
go
ifobject_id('Tempdb..#T')isnotnull
droptable#T
createtable#T(IDint,Namenvarchar(100),parentIDint)
insert#Tselect1,'A',0
insert#Tselect2,'B',1
insert#Tselect3,'C',1
insert#Tselect4,'D',3
insert#Tselect5,'E',2

go

witht2(ID,Name,parentID,Ord,Lev)
as
(select*,Ord=cast(right('0000'+rtrim(ID),4)asnvarchar(max)),Lev=cast(0asint)from#TwhereParentID=0
unionall
selectb.*,Ord=a.Ord+cast(right('0000'+rtrim(b.ID),4)asnvarchar(max)),Lev=cast(a.Lev+1asint)from#Tbjoint2aonb.ParentID=a.ID
)
select顯示=replicate(char(9),lev)+NamefromT2orderbyordoption(maxrecursion0)--用空格替換制表符時用space(lev*2)/replicate(char(9),lev)

/**//*
顯示
---------------------------------
A
B
E
C
D

(5個資料列受到影響)
*/


;witht2
as
(select*,Ord=cast(Nameasnvarchar(max)),lev=0from#TwhereParentID=0
unionall
selectb.*,Ord=cast(a.Ord+char(9)+b.Nameasnvarchar(max)),a.lev+1from#Tbjoint2aonb.ParentID=a.ID
)
select
[顯示]=Ord
from
T2
orderbyord
option(maxrecursion0)
/**//*

顯示
--------------------
A
AB
ABE
AC
ACD

(5個資料列受到影響)

*/

--2005多次调用连接时:
/**//*
;withT(COl)--可指定列名也可不指定
as
(......)
,T2(Co)
as
(......)
select*fromT1ajoinT2bona.Col=b.COl
*/