SQL2005的CTE

来源：www.45fan.com  2019-04-04 05:58:59

use test
go
if object_id('Tempdb..#T') is not null
drop table #T
create table #T(ID int,Name nvarchar(100),parentID int)
insert #T select 1,'A',0
insert #T select 2,'B',1
insert #T select 3,'C',1
insert #T select 4,'D',3
insert #T select 5,'E',2

go

with 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=0
union all
select 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 t2
as
(select *,Ord=cast(Name as nvarchar(max)),lev=0 from #T where ParentID=0
union all
select 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
[顯示]=Ord
from
T2
order by ord
option(maxrecursion 0)
/**//*

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

(5 個資料列受到影響)

*/

--2005多次调用连接时:
/**//*
;with T(COl)--可指定列名也可不指定
as
(......)
,T2(Co)
as
(......)
select * from T1 a join T2 b on a.Col=b.COl
*/