sql2008 树形结构分组

[six-years]

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

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-11-06 12:32:32
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([ID] int,[DeprtID] int,[DeprtName] int)
insert [huang]
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
--------------开始查询--------------------------
;WITH cte2 AS 
(
SELECT * ,ROW_NUMBER()OVER(ORDER BY id)*10 AS [LEVEL]
FROM huang
WHERE deprtid=1
UNION ALL 
SELECT a.id,a.deprtid,a.deprtname,b.[LEVEL]+1 AS [level]
FROM huang a INNER JOIN cte2 b ON a.deprtid=b.id
)
SELECT id,deprtid,deprtname
FROM (
SELECT *,1 AS [level]
FROM huang
WHERE deprtid=0
UNION ALL 
SELECT id,deprtid,deprtname,[LEVEL]
FROM cte2
)a
ORDER BY [level]
----------------结果----------------------------
/* 
id          deprtid     deprtname
----------- ----------- -----------
1           0           1
2           1           2
4           2           4
6           4           6
3           1           3
5           3           5
7           5           7
*/

[-Tracy-McGrady-]

怎么个分法？？？

[LongRui888]

是这样不：

--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
*/