按照节点顺序来排序

[yj_gg]

--> 测试数据: [tree]
if object_id('[tree]') is not null drop table [tree]
create table [tree] (id int,parentid int,classname varchar(3),parentpath varchar(20))
insert into [tree]
select 1,0,'a',',0,' union all
select 2,0,'b',',0,' union all
select 3,1,'ab',',0,1,' union all
select 4,1,'ac',',0,1,' union all
select 5,2,'ba',',0,2,' union all
select 6,3,'abc',',0,1,3,'
go

--结果想变成
id          parentid    classname parentpath
----------- ----------- --------- --------------------
1           0           a         ,0,
3           1           ab        ,0,1,
6           3           abc       ,0,1,3,
4           1           ac        ,0,1,
2           0           b         ,0,
5           2           ba        ,0,2,

[pt1314917]

--> 测试数据: [tree]
if object_id('[tree]') is not null drop table [tree]
create table [tree] (id int,parentid int,classname varchar(3),parentpath varchar(20))
insert into [tree]
select 1,0,'a',',0,' union all
select 2,0,'b',',0,' union all
select 3,1,'ab',',0,1,' union all
select 4,1,'ac',',0,1,' union all
select 5,2,'ba',',0,2,' union all
select 6,3,'abc',',0,1,3,'
go


--2005
;with wsp
as
(
select px=id,lev=cast(1 as varchar),* from tree b where parentid=0 
union all
select px=a.px,lev=cast(a.lev+ltrim(row_number() over(order by b.id)) as varchar),b.* from wsp a,tree b where a.id=b.parentid
)
select id,parentid,classname,parentpath from wsp order by px,lev


--2000的写法
create function get_sort()
returns @t table(id int,pid int,classname varchar(3),parentpath varchar(20),px int,path varchar(20),lev int)
as
begin
	declare @lev int
	set @lev=0
	insert into @t select *,px=id,'1',@lev from tree where parentid=0 
	while(@@rowcount>0)
	begin	
		set @lev=@lev+1
		insert into @t select a.*,b.px,b.path+(select ltrim(count(1)) from tree where pid=a.parentid and id<=a.id),@lev from tree a,@t b
		where a.parentid=b.id and b.lev=@lev-1
	end
	return 
end
go

select id,pid,classname,parentpath from get_sort() order by px,path