/*--表结构描述及数据环境：

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

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

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

--邹建2003.12(引用请保留此信息)--*/

--测试数据

create table tb(id int identity(1,1) not null constraint PK_tb primary key clustered,pid int,name varchar(20))insert 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)asbegin 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  while @@rowcount>0  select @re=right(@idheader+cast(@pid as varchar),@idlen)+','+@re   ,@pid=pid from tb where  return(@re)endgo

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

 --检测

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

lbErr:

 return(@re)endgo

/*--数据复制

 如果表中包含自定义字段,需要修改存储过程

 存在嵌套不超过32层的问题.

--*/

--3.复制指定结点下的子结点到另一个结点下

create proc p_copy@s_id int, --复制该项下的所有子项@d_id int, --复制到此项下@new_id int --新增加项的开始编号asdeclare @nid int,@oid int,@name varchar(20)select id,name into #temp from tb where and id<@new_idwhile 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 endgo

--4.批量复制的存储过程--复制指定结点及其下面的所有子结点,并生成新结点

create proc p_copystr@s_id varchar(8000) --要复制项的列表,用逗号分隔asdeclare @nid int,@oid int,@name varchar(20)set @s_id=','+@s_id+','select id,name into #temp from tbwhere charindex(','+cast(id as varchar)+',', @s_id)>0while 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 endgo

--5.得到指定id的子id列表

create function f_getchildid(@id int)returns @re table(id int)asbegin insert into @re select id from tb where  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) returnendgo

--6.得到指定id的父id列表

create function f_getparentid(@id int)returns @re table(id int)asbegin declare @pid int select @pid=pid from tb where  while @pid<>0 begin  insert into @re values(@pid)  select @pid=pid from tb where  end returnendgo

--7.删除指定结点

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 )   goto lbErr  else   delete from tb where  return

lbErr:

 RAISERROR ('该结点下有子结点,不能删除', 16, 1)go

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

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          endreturnendgo

--应用:

/*--数据显示排序--*/

--分级显示--横向,先一级,后二级...

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 ) print '存在' else print '不存在'

--*/

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

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

Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=20073