在论坛中出现的比较难的sql问题：8(递归问题 树形结构分组)

所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。 

sql2008 树形结构分组
http://bbs.csdn.net/topics/390634930

ID DeprtID DeprtName 
1   0        1        
2   1        2
3   1        3
4   2        4
5   3        5
6   4        6
7   5        7

分组后效果
ID DeprtID DeprtName 
1   0        1        
2   1        2
4   2        4
6   4        6
3   1        3
5   3        5
7   5        7

我的解法：

--drop table tb
 
create table tb(ID int, DeprtID int, DeprtName varchar(10))
 
insert into tb
select 1,   0,        ‘1‘        
union all select 2 ,  1 ,       ‘2‘
union all select 3 ,  1 ,       ‘3‘
union all select 4 ,  2 ,       ‘4‘
union all select 5 ,  3 ,       ‘5‘
union all select 6 ,  4 ,       ‘6‘
union all select 7 ,  5,        ‘7‘
go
 
 
;with t
as
(
select id,DeprtID,DeprtName,1 as level,
       cast(right(‘000‘+cast(id as varchar),3) as varchar(max)) as sort
from tb
where DeprtID =0
 
union all
 
select tb.id,tb.DeprtID,tb.DeprtName,level + 1 ,
       cast(sort+right(‘000‘+cast(tb.id as varchar),3) as varchar(max))
from t
inner join tb 
        on t.id = tb.DeprtID
)
 
select id,deprtid,deprtname
from t
order by sort
/*
id	deprtid	deprtname
1	0	    1
2	1	    2
4	2	    4
6	4	    6
3	1	    3
5	3	    5
7	5	    7
*/

这里还有个例子，就是递归查询后，按照树形来排序：

drop table tb
 
create table tb
(
id int,
pid int,
name varchar(20)
)
 
insert into tb
select 1,null,‘x‘ 
union all select 2,1,‘a‘
union all select 3,1,‘b‘
union all select 4,2,‘aa‘
union all select 5,3,‘bb‘
go
 
 
;with t
as
(
select id,pid,name,1 as level,
       cast(right(‘000‘+cast(id as varchar),3) as varchar(max)) as sort
from tb
where pid is null
 
union all
 
select tb.id,tb.pid,tb.name,level + 1 ,
       cast(sort+right(‘000‘+cast(tb.id as varchar),3) as varchar(max))
from t
inner join tb 
        on t.id = tb.pid
)
 
select *
from t
order by sort
/*
id	pid	name	level	sort
1	NULL	x	1	001
2	1	a	2	001002
4	2	aa	3	001002004
3	1	b	2	001003
5	3	bb	3	001003005
*/