最近，在论坛中，遇到了不少比较难的sql问题，虽然自己都能解决，但发现过几天后，就记不起来了，也忘记解决的方法了。
所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。
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
*/