SQLServer2000 实现多级递归查询

Question

以前递归都是先读出父菜单数据到java中处理的，现在碰到要在sql中直接查询出递归的结果，网上有很多递归的例子但都只到第一级，我的是不定级数的，如： 数据库是Sql Server 2000 的
 id pid name 
1 0 福建 
2 0 浙江 
3 1 厦门 
4 2 绍兴 
5 1 三明 
6 3 思明区 
这个是原始表数据 查询后结果变成： 
id pid name 
1 0 福建 
3 1 厦门 
6 3 思明区 
5 1 三明 
2 0 浙江 
4 2 绍兴 好像要创建sql 方法来解决，哪位有做过的，望指点 

Answer 1

--建立 演示环境if object_id('tb_bookInfo') is not null drop table tb_bookInfo
go
create table tb_bookInfo(number int,name varchar(10),type int)
insert tb_bookInfo
select 1 ,'n1', 6 union all
select 2 ,'n2', 3
if object_id('tb_bookType') is not null drop table tb_bookType
go
create table tb_bookType(id int,typeName varchar(10),parentid int)
insert tb_bookType
select 1,'英语',0 union all
select 2,'生物',0 union all
select 3,'计算机',0 union all
select 4,'口语',1 union all
select 5,'听力',1 union all
select 6,'数据库',3 union all
select 7,'软件工程',3 union all
select 8,'SQL Server',6select a.*,b.level from tb_bookInfo  a,f_getC(3) b  where a.type=b.id  order by b.level 
/*
number      name       type        level      
----------- ---------- ----------- -----------
2           n2         3           0
1           n1         6           1（所影响的行数为 2 行）
*/
--查所有父结点
if object_id('f_getP') is not null drop function f_getP
go
create function f_getP(@id int) 
returns @re table(id int,level int) 
as 
begin
    declare @l int 
    set @l=0 
    insert @re select @id,@l 
    while @@rowcount>0 
    begin 
 set @l=@l+1
 insert @re select a.parentid,@l from tb_bookType a,@re b
 where a.id=b.id and b.level=@l-1 and a.parentid<>0
    end 
    update @re set level=@l-level
    return 
end 
go 
--查所有子结点
if object_id('f_getC') is not null drop function f_getC
go
create function f_getC(@id int) 
returns @re table(id int,level int) 
as 
begin
    declare @l int 
    set @l=0 
    insert @re select @id,@l 
    while @@rowcount>0
    begin 
        set @l=@l+1
        insert @re select a.id,@l from tb_bookType as a,@re as b 
 where b.id=a.parentid and b.level=@l-1
    end
    return 
end 
go --查所有父子结点
if object_id('f_getAll') is not null drop function f_getAll
go
create function f_getAll(@id int) 
returns @re table(id int,level int) 
as 
begin 
    declare @l int 
    set @l=0 
    insert @re select @id,@l 
    while @@rowcount>0 
    begin 
 set @l=@l+1
 insert @re select a.parentid,@l from tb_bookType a,@re b
 where a.id=b.id and b.level=@l-1 and a.parentid<>0
    end 
    update @re set level=@l-level 
    while @@rowcount>0
    begin 
        set @l=@l+1
        insert @re select a.id,@l from tb_bookType as a,@re as b 
 where b.id=a.parentid and b.level=@l-1
    end
    return
end 
go  
 --删除演示drop table tb_bookInfodrop table tb_bookTypedrop function f_getPdrop function f_getC
drop function f_getAll

Answer 2

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([modeid] int,modename varchar(20),parentid int)
insert [tb]
select 100 ,'商品管理', 0 union all
select 101 ,'定单管理', 0 union all
select 102 ,'用户管理', 0 union all
select 104 ,'学院广告', 0 union all
select 105 ,'系统设置', 0 union all
select 106 ,'附件管理', 0 union all
select 107 ,'商品管理', 100 union all
select 108 ,'明细管理', 100 union all
select 109 ,'物流管理', 100 union all
select 110 ,'商品信息管理', 107 union all
select 111 ,'商品分类管理', 107 union all
select 112 ,'回收站管理', 107 union all
select 114 ,'团购管理', 108 union all
select 115 ,'拍卖管理', 108 union all
select 116 ,'优惠管理', 108 union all
select 117 ,'会员管理', 102 union all
select 118 ,'会员卡管理', 102 union all
select 119 ,'资金管理', 102 union all
select 120 ,'管理员管理', 102 union all
select 121 ,'添加管理员', 120 union all
select 122 ,'修改管理员', 120
go
--查所有子结点
if object_id('f_getC') is not null drop function f_getC
go
create function f_getC(@id int) 
returns @re table(id int,level int,sort varchar(10)) 
as 
begin
    declare @l int 
    set @l=0 
    insert @re select @id,@l,null
    while @@rowcount>0
    begin 
        set @l=@l+1
        insert @re select a.modeid,@l,ltrim(isnull(b.sort,a.modeid)) from tb as a,@re as b 
 where b.id=a.parentid and b.level=@l-1
    end
    update @re set level = level -1
    return 
end 
go 
select a.modeid,a.parentid,REPLICATE('  ',b.level) +'┝'+a.modename,b.level,b.sort from tb  a,f_getC(0) b 
where a.modeid=b.id 
order by case when b.level<2 then 0 else 1 end,b.sort,b.level/*
modeid      parentid                                                       sort       level      
----------- ----------- -------------------------------------------------- ---------- -----------
100         0           ┝商品管理                                              100        0
107         100           ┝商品管理                                            100        1
108         100           ┝明细管理                                            100        1
109         100           ┝物流管理                                            100        1
101         0           ┝定单管理                                              101        0
102         0           ┝用户管理                                              102        0
117         102           ┝会员管理                                            102        1
118         102           ┝会员卡管理                                           102        1
119         102           ┝资金管理                                            102        1
120         102           ┝管理员管理                                           102        1
104         0           ┝学院广告                                              104        0
105         0           ┝系统设置                                              105        0
106         0           ┝附件管理                                              106        0
110         107             ┝商品信息管理                                        100        2
111         107             ┝商品分类管理                                        100        2
112         107             ┝回收站管理                                         100        2
114         108             ┝团购管理                                          100        2
115         108             ┝拍卖管理                                          100        2
116         108             ┝优惠管理                                          100        2
121         120             ┝添加管理员                                         102        2
122         120             ┝修改管理员                                         102        2（所影响的行数为 21 行）*/
--查所有子结点,带路径与排序
if object_id('f_getC') is not null drop function f_getC
go
create function f_getC(@id int) 
returns @re table(id int,level int,sort varchar(100),path varchar(500)) 
as 
begin
    declare @l int 
    set @l=0 
    insert @re
 select [modeid],@l,right('00000'+ltrim(modeid),5),modename
 from tb where parentid=@id
    while @@rowcount>0
    begin 
        set @l=@l+1
        insert @re
  select a.modeid,@l,b.sort+right('00000'+ltrim(a.modeid),5),
      b.path+' - '+a.modename
  from tb as a,@re as b 
  where b.id=a.parentid and b.level=@l-1
    end
    update @re set level = level
    return 
end 
go select a.modeid,a.parentid,REPLICATE('  ',b.level) +'┝'+a.modename,b.level,b.sort ,b.path from tb  a,f_getC(0) b 
where a.modeid=b.id 
order by sort/*
modeid      parentid                         level                           
----------- ----------- -------------------- ----------- -------------------- ----------------------------------------
100         0           ┝商品管理                0           00100                商品管理
107         100           ┝商品管理              1           0010000107           商品管理 - 商品管理
110         107             ┝商品信息管理          2           001000010700110      商品管理 - 商品管理 - 商品信息管理
111         107             ┝商品分类管理          2           001000010700111      商品管理 - 商品管理 - 商品分类管理
112         107             ┝回收站管理           2           001000010700112      商品管理 - 商品管理 - 回收站管理
108         100           ┝明细管理              1           0010000108           商品管理 - 明细管理
114         108             ┝团购管理            2           001000010800114      商品管理 - 明细管理 - 团购管理
115         108             ┝拍卖管理            2           001000010800115      商品管理 - 明细管理 - 拍卖管理
116         108             ┝优惠管理            2           001000010800116      商品管理 - 明细管理 - 优惠管理
109         100           ┝物流管理              1           0010000109           商品管理 - 物流管理
101         0           ┝定单管理                0           00101                定单管理
102         0           ┝用户管理                0           00102                用户管理
117         102           ┝会员管理              1           0010200117           用户管理 - 会员管理
118         102           ┝会员卡管理             1           0010200118           用户管理 - 会员卡管理
119         102           ┝资金管理              1           0010200119           用户管理 - 资金管理
120         102           ┝管理员管理             1           0010200120           用户管理 - 管理员管理
121         120             ┝添加管理员           2           001020012000121      用户管理 - 管理员管理 - 添加管理员
122         120             ┝修改管理员           2           001020012000122      用户管理 - 管理员管理 - 修改管理员
104         0           ┝学院广告                0           00104                学院广告
105         0           ┝系统设置                0           00105                系统设置
106         0           ┝附件管理                0           00106                附件管理(21 行受影响)
*/----------