相關(guān)討論連接:
早就想簡單說說: 關(guān)于樹型結(jié)構(gòu)數(shù)據(jù)的存儲及維護http://expert.csdn.net/expert/topicview1.asp?id=1677669
樹型結(jié)構(gòu)數(shù)據(jù)的存儲采用:
tree(id,parentid,remark)
如果僅對于存儲來講,無疑是最經(jīng)濟!
但是利用這樣的結(jié)構(gòu),來提供一些基于稍微復(fù)雜點的查詢的應(yīng)用表現(xiàn)形式
效率應(yīng)該說相當(dāng)?shù)拖?
如: 查詢某節(jié)點的路徑等!
如要高效的查詢,我們可以在維護數(shù)據(jù)時下點功夫!
我們以一個樹型結(jié)構(gòu)論壇的實現(xiàn)為例:
tree(id,parentid,rootid,orderid,maxid,indent,title,content,remark)
id: integer 帖子id
parentid: integer 父貼id
rootid: integer 根帖id
orderid: integer 同一個根帖中,帖子順序id
maxid: integer 用于使新貼在頂部
indent: integer 縮進量
title: varchar 帖子標(biāo)題
content: varchar 帖子內(nèi)容
remark: varchar 除 id,parentid 外的貼子線索
這樣的設(shè)計只要維護好每一個字段都為查詢顯示提高了效率!
請看下面的維護程序:
--==========================================
alter procedure appsp_addnew
@id integer
,@title varchar(8000) =null
,@content varchar(8000)=null
as
--declare @id int
--set @id=0
if @id=0
begin
insert into tree (parentid,orderid,indent,title,content)
values (0,0,0,@title,@content)
--把帖子頂?shù)缴厦?
update tree
set rootid = id
,maxid = (select max(id) from tree)
where rootid is null
end
else
begin
--調(diào)整同一個"根帖"中,帖子的內(nèi)部順序:
update tree
set orderid = orderid + 1
where rootid = (select rootid
from tree
where id = @id)
and orderid > (select orderid
from tree
where id = @id
)
--插入回復(fù)的帖子,同時維護 rootid,parentid,orderid,indent,remark,title,content
insert into tree (rootid,parentid,orderid,indent,remark,title,content)
select rootid,@id,orderid+1,indent + 1
,case when remark is null then cast(parentid as varchar)
else remark + '-' + cast(parentid as varchar)
end
,isnull(@title,'re: ' + title),@content
from tree
where [email protected]
--把帖子頂?shù)缴厦?
update tree
set maxid = (select max(id)
from tree
)
where rootid = (select rootid
from tree
where [email protected]
)
end
--========================================
該程序用于
1.增加新貼:
appsp_addnew 0,'第一個問題','地球是圓的嗎?'
2.回復(fù)帖子:
appsp_addnew 1,'re: 第一個問題','地球是圓的!'
這樣,只需簡單查詢:
select *, remark + '-' + cast(parentid as varchar) + '-' + cast(id as varchar) , space(indent) + '['
from tree
order by maxid desc,orderid
就可高效的實現(xiàn)帖子列表及其線索,級別等!
雖然維護時增加了一些工作量!
--相關(guān)ddl腳本:
create table [tree] (
[id] [int] identity (1, 1) not null ,
[parentid] [int] null ,
[rootid] [int] null ,
[orderid] [int] null ,
[maxid] [int] null ,
[indent] [int] null ,
[title] [varchar] (50),
[content] [varchar] (200) ,
[remark] [varchar] (250) ,
constraint [pk_tree] primary key clustered
(
[id]
) on [primary]
) on [primary]
