日期:2014-05-17 浏览次数:20473 次
--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
*/