，歡迎訪問網頁設計愛好者web開發。
描述：討論如何處理樹形數據，排序,新增,修改,復制,刪除,數據完整性檢查,匯總統計
表結構描述及數據環境：
表名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 [email protected]
while @@rowcount>0
select @re=right(@idheader+cast(@pid as varchar),@idlen)+','[email protected]
,@pid=pid from tb where [email protected]
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 [email protected]
while @@rowcount>0
begin
if @[email protected]
begin
set @re=1
goto lberr
end
select @pid=pid from tb where [email protected]
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 [email protected]_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 @[email protected]@identity
exec p_copy @oid,@nid,@new_id
delete from #temp where [email protected]
end
go
--4.批量復制的存儲過程--復制指定結點及其下面的所有子結點,并生成新結點
create proc p_copystr
@s_id varchar(8000) --要復制項的列表,用逗號分隔
as
declare @nid int,@oid int,@name varchar(20)
set @s_id=','[email protected]_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 @[email protected]@identity
exec p_copy @oid,@nid,@nid
delete from #temp where [email protected]
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 [email protected]
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 [email protected]
while @pid<>0
begin
insert into @re values(@pid)
select @pid=pid from tb where [email protected]
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 [email protected])
goto lberr
else
delete from tb where [email protected]
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 @[email protected]+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 [email protected]
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 [email protected]) 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
