SQLServer2005中的CTE递归查询得到一棵树

 1 use City;
 2 go
 3 create table Tree
 4 (
 5 ID int identity(1,1) primary key not null,
 6 Name varchar(20) not null,
 7 Parent varchar(20) null
 8 )
 9 go
10 insert Tree values('大学',null)
11 insert Tree values('学院','大学')
12 insert Tree values('计算机学院','学院')
13 insert Tree values('网络工程','计算机学院')
14 insert Tree values('信息管理','计算机学院')
15 insert Tree values('电信学院','学院')
16 insert Tree values('教务处','大学')
17 insert Tree values('材料科','教务处')
18 insert Tree values('招生办','大学')
19 go
20 with CTE as
21 (
22 -->Begin 一个定位点成员
23 select ID, Name,Parent,cast(Name as nvarchar(max)) as TE,0 as Levle from Tree where Parent is null
24 -->End 
25 union all
26 -->Begin一个递归成员
27 select Tree.ID, Tree.Name,Tree.Parent,cast(replicate(' ',len(CTE.TE))+'|_'+Tree.name as nvarchar(MAX)) as TE,Levle+1 as Levle
28 from Tree inner join CTE
29 on Tree.Parent=CTE.Name
30 -->End
31 )
32 select * from CTE order by ID
33 --1.将 CTE 表达式拆分为定位点成员和递归成员。
34 --2.运行定位点成员，创建第一个调用或基准结果集 (T0)。
35 --3.运行递归成员，将 Ti 作为输入(这里只有一条记录)，将 Ti+1 作为

1 select ID, Name,Parent,cast(Name as nvarchar(max)) as TE,0 as Levle from Tree where Parent is null

1 select Tree.ID, Tree.Name,Tree.Parent,cast(replicate(' ',len(CTE.TE))+'|_'+Tree.name as nvarchar(MAX)) as TE,Levle+1 as Levle
2 from Tree inner join
3 (select ID, Name,Parent,cast(Name as nvarchar(max)) as TE,0 as Levle from Tree where Parent is null)
4 as CTE
5 on Tree.Parent=CTE.Name

1 select Tree.ID, Tree.Name,Tree.Parent,cast(replicate(' ',len(CTE.TE))+'|_'+Tree.name as nvarchar(MAX)) as TE,Levle+1 as Levle
2 from Tree inner join
3 (第二次递归查询的SQL语句)as CTE
4 on Tree.Parent=CTE.Name

1 select Tree.ID, Tree.Name,Tree.Parent,cast(replicate(' ',len(CTE.TE))+'|_'+Tree.name as nvarchar(MAX)) as TE,Levle+1 as Levle
2 from Tree inner join
3 (上次递归查询的结果集，仅仅是上次那一次的，而不是以前的总和结果集)
4 as CTE
5 on Tree.Parent=CTE.Name

 1 with CTE as 
 2 ( 
 3 -->Begin 一个定位点成员 
 4 select ID, Name,Parent,cast(Name as nvarchar(max)) as TE,
 5 ROW_NUMBER()over(order by getdate()) as OrderID
 6 --最关键是上面这个字段，要获取排序字段，按字符串来排序。
 7 --其中窗口函数必须要使用order by，但是不能用整型，那就用时间吧
 8 from Tree where Parent is null 
 9 -->End 
10 union all 
11 -->Begin一个递归成员 
12 select Tree.ID, Tree.Name,Tree.Parent,cast(replicate(' ',len(CTE.TE))+'|_'+Tree.name as nvarchar(MAX)) as TE,
13 CTE.OrderID*100+ROW_NUMBER()over(Order by GETDATE()) as OrderID
14 from Tree inner join CTE 
15 on Tree.Parent=CTE.Name 
16 -->End 
17 ) 
18 select * from CTE
19 order by LTRIM(OrderID)--最后将这个整型数据转换为字符串型的进行排序
20 
21 --有时候整型可以比大小，字符串也可以，字符串比的大小是一位一位进行字符比较的
22 --整型+字符串==整型，只有字符串+字符串==两个字符串的并和
23 --递归查询中：第二条记录可以引用第一条记录的值
24 --动态加载记录时，同一个等级的记录识别符：RowNumber()over(order by getdate())
25 --延伸：可以动态获取某个部门下的所以子部门。也可以获取该部门上级的所以部门
26 
27 --总结：首先要拼凑出一个整型数据，然后转换为字符串，最后是进行字符串的order，而不是整型数据的order，