树形数据的处理

描述：讨论如何处理树形数据，排序,新增,修改,复制,删除,数据完整性检查,汇总统计

表结构描述及数据环境：

         表名tb,如果修改表名,则相应修改所有数据处理中涉及到的表名tb

         id为编号(标识字段+主键)，pid为上级编号，name为名称,后面可以自行增加其他字段.

         凡是未特殊标注的地方,对自行增加的字段不影响处理结果/*--数据测试环境
 表名tb,如果修改表名,则相应修改所有数据处理中涉及到的表名tb
 id为编号(标识字段+主键)
 pid为上级编号
 name为名称,后面可以自行增加其他字段.

 凡是未特殊标注的地方,对自行增加的字段不影响处理结果


--表环境

create table tb(id int identity(1,1) not null constraint PK_tb primary key clustered
 ,pid int,name varchar(20))
insert into tb
 select 0,@#中国@#
 union all select 0,@#美国@#
 union all select 0,@#加拿大@#
 union all select 1,@#北京@#
 union all select 1,@#上海@#
 union all select 1,@#江苏@#
 union all select 6,@#苏州@#
 union all select 7,@#常熟@#
 union all select 6,@#南京@#
 union all select 6,@#无锡@#
 union all select 2,@#纽约@#
 union all select 2,@#旧金山@#
go


--处理中需要使用的函数及存储过程

--1.自定义函数--获取编码累计
create function f_getmergid(@id int)
returns varchar(8000)
as
begin
 declare @re varchar(8000),@pid int

 --为了数字排序正常,需要统一编码宽度
 declare @idlen int,@idheader varchar(20)
 select @idlen=max(len(id))
  ,@idheader=space(@idlen)
 from tb

 --得到编码累计
 set @re=right(@idheader+cast(@id as varchar),@idlen)
 select @pid=pid from tb where id=@id
 while @@rowcount>0
  select @re=right(@idheader+cast(@pid as varchar),@idlen)+@#,@#+@re
   ,@pid=pid from tb where id=@pid
 return(@re)
end
go


--2.自定义函数--检测某个编码出发,是否被循环引用
create function f_chkid(@id int)
returns bit --循环,返回1,否则返回0
as
begin
 declare @re bit,@pid int
 
 set @re=0

 --检测
 select @pid=pid from tb where id=@id
 while @@rowcount>0
 begin
  if @pid=@id
  begin
   set @re=1
   goto lbErr
  end
  select @pid=pid from tb where id=@pid
 end

lbErr:
 return(@re)
end
go

/*--数据复制

 如果表中包含自定义字段,需要修改存储过程
 存在嵌套不超过32层的问题.
--*/

--3.复制指定结点下的子结点到另一个结点下
create proc p_copy
@s_id int, --复制该项下的所有子项
@d_id int, --复制到此项下
@new_id int --新增加项的开始编号
as
declare @nid int,@oid int,@name varchar(20)
select id,name into #temp from tb where pid=@s_id and id<@new_id
while exists(select 1 from #temp)
begin
 select @oid=id,@name=name from #temp
 insert into tb values(@d_id,@name)
 set @nid=@@identity
 exec p_copy @oid,@nid,@new_id
 delete from #temp where id=@oid
end
go

--4.批量复制的存储过程--复制指定结点及其下面的所有子结点,并生成新结点
create proc p_copystr
@s_id varchar(8000) --要复制项的列表,用逗号分隔
as
declare @nid int,@oid int,@name varchar(20)
set @s_id=@#,@#+@s_id+@#,@#
select id,name into #temp from tb
where charindex(@#,@#+cast(id as varchar)+@#,@#, @s_id)>0
while exists(select 1 from #temp)
begin
 select @oid=id,@name=name from #temp
 insert into tb values(@oid,@name)
 set @nid=@@identity
 exec p_copy @oid,@nid,@nid
 delete from #temp where id=@oid
end
go

--6.得到指定id的子id列表
create function f_getchildid(@id int)
returns @re table(id int)
as
begin
 insert into @re select id from tb where pid=@id
 while @@rowcount>0
  insert into @re select a.id
   from tb a inner join @re b on a.pid=b.id
   where a.id not in(select id from @re)
 return
end
go


--7.得到指定id的父id列表
create function f_getparentid(@id int)
returns @re table(id int)
as
begin
 declare @pid int
 select @pid=pid from tb where id=@id
 while @pid<>0
 begin
  insert into @re values(@pid)
  select @pid=pid from tb where id=@pid
 end
 return
end
go


--8.删除指定结点

create proc p_delete
@id int,    --要删除的id
@deletechild bit=0  --是否删除子 1.删除子,0.如果@id有子,则删除失败.
as
 if @deletechild=1
  delete from tb where dbo.f_getmergid(id) like dbo.f_getmergid(@id)+@#%@#
 else
  if exists(select 1 from tb where pid=@id)
   goto lbErr
  else
   delete from tb where id=@id
 
return

lbErr:
 RAISERROR (@#该结点下有子结点,不能删除@#, 16, 1)
go


--9.得到编码累计及编码级别表,这个是针对全表的,主要是应该于全表处理：

create function f_getbmmerg()

returns @re table(id int,idmerg varchar(8000),level int)

as

begin

         declare @idlen int,@idheader varchar(20), @level int

         select @idlen=max(len(id)),@idheader=space(@idlen) from tb

         set @level=1

         insert into @re select id,right(@idheader+cast(id as varchar),@idlen),@level

                   from tb where pid=0

         while @@rowcount>0

         begin

                   set @level=@level+1

                   insert into @re select b.id,a.idmerg+@#,@#+right(@idheader+cast(b.id as varchar),@idlen),@level

                            from @re a inner join tb b on a.id=b.pid

                            where a.level=@level-1

 

         end

return

end

go


--应用:

/*--数据显示排序--*/
--分级显示--横向,先一级,后二级...
select * from tb order by pid

--分级显示--纵向
select * from tb order by dbo.f_getmergid(id)
go

/*--数据统计--*/
--分级统计,每个地区下的明细地区数
select *,
 明细地区数=(select count(*) from tb where dbo.f_getmergid(id) like dbo.f_getmergid(a.id)+@#,%@#)
from tb a order by dbo.f_getmergid(id)

go
/*--数据新增,修改
 
 数据新增,修改(包括修改所属的类别)没有什么技巧
 ,只需要检查所属的上级是否存在就行了.这个可以简单的用下面的语句来解决:
 if exists(select 1 from tb where id=@id) print @#存在@# else print @#不存在@#
--*/


--删除@#美国@#的数据
--exec p_delete 2  --不包含子,因为有美国下有子,所以删除会出错
exec p_delete 2,1 --包含子,将删除美国及所有数据
go


原文参见我在CSDN上发表的贴子

http://expert.csdn.net/Expert/topic/2285/2285830.xml?temp=.1212885

原文转自：http://www.ltesting.net