sql2008 树形结构分组
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
/*
iddeprtiddeprtname
10 1
21 2
42 4
64 6
31 3
53 5
75 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
*/