SQL Server 中树形表数据的处理总结

-- 使用函数的方法：

--建立 演示环境

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',6

select 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.par