use test go ifobject_id('Tempdb..#T') isnotnull droptable #T createtable #T(ID int,Name nvarchar(100),parentID int) insert #T select1,'A',0 insert #T select2,'B',1 insert #T select3,'C',1 insert #T select4,'D',3 insert #T select5,'E',2 go with t2(ID,Name,parentID,Ord,Lev) as (select*,Ord=cast(right('0000'+rtrim(ID),4) asnvarchar(max)),Lev=cast(0asint) from #T where ParentID=0 unionall select b.*,Ord=a.Ord+cast(right('0000'+rtrim(b.ID),4) asnvarchar(max)),Lev=cast(a.Lev+1asint)from #T b join t2 a on b.ParentID=a.ID ) select 顯示=replicate(char(9),lev)+Name from T2 orderby ord option(maxrecursion 0)--用空格替換制表符時用space(lev*2)/replicate(char(9),lev) /**//* 顯示 --------------------------------- A B E C D (5 個資料列受到影響) */ ;with t2 as (select*,Ord=cast(Name asnvarchar(max)),lev=0from #T where ParentID=0 unionall select b.*,Ord=cast(a.Ord+char(9)+b.Name asnvarchar(max)),a.lev+1from #T b join t2 a on b.ParentID=a.ID ) select [顯示]=Ord from T2 orderby 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 */
posted on
2008-05-16 13:46中國風
阅读(112)
评论(0)
编辑收藏举报