日期:2014-05-17 浏览次数:20470 次
if object_id('tempdb..#tb') is not null drop table #tb
go
create table #tb(id int,[name] varchar(3),parent_id varchar(3))
insert #tb
select 1,'w',null union all
select 3,'d',null union all
select 5,'d',1 union all
select 6,'f',3 union all
select 7,'f',1 union all
select 8,'g',null union all
select 9,'g',null
;with cte as(
select *,sort=Convert(varchar(10),id) from #tb where parent_id is null
union all
select a.* ,sort=Convert(varchar(10),b.sort+Convert(varchar(10),a.id)) from #tb a,cte b where b.id=a.parent_id
)
select id,name,parent_id from cte order by sort
--查询结果如下
/*
id name parent_id
----------- ---- ---------
1 w NULL
5 d 1
7 f 1
3 d NULL
6 f 3
8 g NULL
9 g NULL
*/
if object_id('tempdb..#tb') is not null drop table #tb
go
create table #tb(id int,[name] varchar(3),parent_id varchar(3))
insert #tb
select 1,'w',null union all
select 3,'d',null union all
select 5,'d',1 union all
select 6,'f',3 union all
select 7,'f',1 union all
select 8,'g',null union all
select 9,'g',null
select id,name,parent_id
from #tb
order by case when parent_id is null then id else parent_id end,id
/*
id name parent_id
1 w NULL
5 d 1
7 f 1
3 d NULL
6 f 3
8 g NULL
9 g NULL
*/
create table nay
(id int,name varchar(10),parent_id int)
insert into nay
select 1,'w',null union all
select 3,'d',null union all
select 5,'d',1 union all
select 6,'f',3 uni