![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
IF OBJECT_ID('tempdb..#') IS NOT NULL
drop table #
create table #
(ClassID int,
ClassName nvarchar(20),
ParentClassID int,
CreateTime datetime)
insert into #
select 1, N'基础任务',0,'2009-06-29'
union
select 2, N'日常协同',0,'2009-06-29'
union
select 3, N'项目支持',0,'2009-06-29'
union
select 4, N'留言',1,'2009-06-29'
union
select 5, N'阿里',4,'2009-06-29'
union
select 6, N'爱上',4,'2009-06-29'
union
select 7, N'是深度',2,'2009-06-29'
union
select 8, N'AASD',2,'2009-06-29'
union
select 9, N'2WEFSD',2,'2009-06-29'
IF OBJECT_ID('tempdb..#sort') IS NOT NULL
drop table #sort
select ClassID, 0 AS LevelID, cast(ClassID as varchar(10)) AS SortID
into #Sort
from #
where parentclassid=0
DECLARE @Level int
SET @Level=0
while @@Rowcount>0
begin
SET @Level = @Level + 1
insert into #sort
select b.classID, @Level, a.SortID + cast(b.classID as varchar(10))from #Sort a
INNER join # b on a.classid=b.parentclassid
WHERE a.LevelID = @Level - 1
end
select * from #Sort order by sortid
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
IF OBJECT_ID('tempdb..#') IS NOT NULL
drop table #
create table #
(ClassID int,
ClassName nvarchar(20),
ParentClassID int,
CreateTime datetime)
insert into #
select 1, N'基础任务',0,'2009-06-29'
union
select 2, N'日常协同',0,'2009-06-29'
union
select 3, N'项目支持',0,'2009-06-29'
union
select 4, N'留言',1,'2009-06-29'
union
select 5, N'阿里',4,'2009-06-29'
union
select 6, N'爱上',4,'2009-06-29'
union
select 7, N'是深度',2,'2009-06-29'
union
select 8, N'AASD',2,'2009-06-29'
union
select 9, N'2WEFSD',2,'2009-06-29'
;with t as
(
select *, 1 as IndexID, classid px from # where ParentClassID=0
union all
select a.*, b.IndexID+1, b.px from # a,t b where a.ParentClassID=b.ClassID
)
select *
from t order by px, IndexID, ClassID