请教个关于SQL递归排序的方法

[caishbian]

我有一张表 a
里面字段是 id,pid,level(层数)
数据是如 1,0,0
2,0,0
3,0,0
4,2,1
5,2,2
6,2,3
7,3,1

我要怎么用SQL排序成先按ID排，如果此ID的PID不等0则下一个是按层数排序
想要的查询结果如

数据是如 1,0,0
2,0,0
4,2,1
5,2,2
6,2,3
3,0,0
7,3,1

[百年树人]

select * from tb order by case when pid!=0 then pid else id end,level

适合只有两级的排序

[caishbian]

[Quote=引用 1 楼 josy 的回复:]
SQL code
select * from tb order by case when pid!=0 then pid else id end,level


适合只有两级的排序
[/Quote]

谢谢，如果不止二级应该怎么办

[gengchenhui]

哦，是想要一个树的结构是么？在程序中实现，不要在数据库中实现。

[billpu]

[Quote=引用 2 楼 caishbian 的回复:]

引用 1 楼 josy 的回复:
SQL code
select * from tb order by case when pid!=0 then pid else id end,level


适合只有两级的排序


谢谢，如果不止二级应该怎么办
[/Quote]
那写存储过程或者函数
用while...递归

[xman_78tom]

declare @t table (id int,pid int,lvl int);
insert into @t values(1,0,0),(2,0,0),(3,0,0),
(4,2,1),(5,2,2),(6,2,3),(7,3,1),(8,4,1),(9,4,2),(10,3,2);

with t as (
select id,pid,lvl,cast(right('000'+ltrim(id),4) as varchar(100)) [path] from @t where pid=0
union all
select r.id,r.pid,r.lvl,
cast([path]+','+right('000'+ltrim(r.id),4) as varchar(100))
from @t r, t where t.id=r.pid
)
select id,pid,lvl from t order by [path],lvl;
/*
1	0	0
2	0	0
4	2	1
8	4	1
9	4	2
5	2	2
6	2	3
3	0	0
7	3	1
10	3	2
*/

[永生天地]

树形表数据的处理总结
http://blog.csdn.net/xys_777/archive/2010/06/15/5672481.aspx
仅参考

[dawugui]

--sql 2005

create table tb (id int,pid int,lvl int)
insert into tb values(1,0,0)
insert into tb values(2,0,0)
insert into tb values(3,0,0)
insert into tb values(4,2,1)
insert into tb values(5,2,2)
insert into tb values(6,2,3)
insert into tb values(7,3,1)
go

;with t as 
(
    select *,px=cast(id as varbinary(max)) from tb 
    where [pid]=0
    union all
    select a.*,px+cast(a.id as varbinary)  
    from tb a 
        join t b on a.[pid]=b.id
)
select id,[pid],lvl
from t
order by px

drop table tb

/*
id          pid         lvl
----------- ----------- -----------
1           0           0
2           0           0
4           2           1
5           2           2
6           2           3
3           0           0
7           3           1

(7 行受影响)
*/

[dawugui]

--sql 2000

create table tb (id int,pid int,lvl int)
insert into tb values(1,0,0)
insert into tb values(2,0,0)
insert into tb values(3,0,0)
insert into tb values(4,2,1)
insert into tb values(5,2,2)
insert into tb values(6,2,3)
insert into tb values(7,3,1)
go

create function f_tree(@id int) returns varchar(1000)
as
begin
  declare @ret varchar(1000) , @pid int
  set @ret = rtrim(@id)
  while exists(select 1 from tb where id = @id and pid <> 0 )
  begin
    select @pid = pid from tb where id = @id and pid <> 0
    set @id = @pid
    set @ret = rtrim(@id)+ @ret
  end
  return @ret
end
go

select * from tb order by dbo.f_tree(id)
/*
id          pid         lvl         
----------- ----------- ----------- 
1           0           0
2           0           0
4           2           1
5           2           2
6           2           3
3           0           0
7           3           1

（所影响的行数为 7 行）
*/

drop function f_tree
drop table tb