现有数据表（Ttable）结构（两个字段均为整型）及记录如下：
id reid
1 0
2 0
3 0
4 2
5 2
6 1
7 4要求查询的记录排序成如下:
id reid
1 0
6 1
2 0
4 2
7 4
5 2
3 0（注：id为自增长，reid为回复那条记录id的值）谢谢！
id reid
1 0
2 0
3 0
4 2
5 2
6 1
7 4要求查询的记录排序成如下:
id reid
1 0
6 1
2 0
4 2
7 4
5 2
3 0（注：id为自增长，reid为回复那条记录id的值）谢谢！
create table A
(
id int,
upid int,
name varchar(10)
)
insert A
select 1, 0, 'aaa' union
select 2, 0, 'bbb' union
select 3, 2, 'cccc' union
select 4, 1, 'ddddd' union
select 5, 2, 'eeeee' union
select 6, 1, 'fffff' union
select 7, 4, 'gggg' union
select 8, 4, 'hhhhh'
go
--创建函数
create function f_level(@id int,@level int)
returns varchar(20)
as
begin
declare @upid int
set @id=isnull(@id,0)
select @upid=upid from A where id=@id
if not exists(select 1 from A where id=@upid)
return convert(varchar,@id)+'.'+convert(varchar,@level)
return dbo.f_level(@upid,@level+1)
end
go
--查询
select * from A
order by parsename(dbo.f_level(id,0),2)
,parsename(dbo.f_level(id,0),1)
--删除测试环境
drop function f_level
drop table A
--结果
/*
id upid name
----------- ----------- ----------
1 0 aaa
4 1 ddddd
6 1 fffff
7 4 gggg
8 4 hhhhh
2 0 bbb
3 2 cccc
5 2 eeeee
(8 row(s) affected)
*/
sql2000下只能是函数，或自己写循环了。
create table Ttable(id int,reid int)
insert Ttable
select 1, 0 union all
select 2, 0 union all
select 3, 0 union all
select 4, 2 union all
select 5, 2 union all
select 6, 1 union all
select 7, 4
go with cte as
(
select id,reid,rn=cast(right(10000+row_number() over(order by id),4) as varchar(max))
from Ttable where reid=0
union all
select a.id,a.reid,rn=b.rn+right(10000+row_number() over(order by a.id),4)
from Ttable a join cte b on a.reid=b.id
)
select id,reid from cte order by rn/*
id reid
----------- -----------
1 0
6 1
2 0
4 2
7 4
5 2
3 0
*/