|
|
Posted on 2006-10-15 03:16 大大毛 閱讀(887) 評(píng)論(0) 編輯 收藏 所屬分類(lèi): SQL
???樹(shù)型結(jié)構(gòu)在應(yīng)用中被廣泛的運(yùn)用，在數(shù)據(jù)操作上需要一定的技巧來(lái)進(jìn)行處理：
???在表結(jié)構(gòu)的定義上，有多種方式可以采用。
??.數(shù)據(jù)表：
??????a.單向鏈表式的結(jié)構(gòu) ( 這是藕給它起的名稱(chēng)，因?yàn)樗c鏈表的結(jié)構(gòu)相似，除了必要的自身描述外，就只包含父節(jié)點(diǎn)的位置)
?????????DDL示例：
--相當(dāng)于單向鏈表
create?table?linkTree?(
????id?varchar(10)?primary?key,?????????--節(jié)點(diǎn)自身ID
????nodeContent?varchar(50),??????????--節(jié)點(diǎn)內(nèi)容
????parentId?varchar(10)?default?''????--父節(jié)點(diǎn)ID
)
?????????優(yōu)點(diǎn)：
????????????.列寬固定，表結(jié)構(gòu)易維護(hù)；
????????????.查找指定節(jié)點(diǎn)的上/下游節(jié)點(diǎn)效率高；
????????????.維護(hù)樹(shù)型結(jié)構(gòu)方便(增/刪層)；
?????????缺點(diǎn)，如在下列情況中，都需要多次Select完成：
????????????.從指定節(jié)點(diǎn)開(kāi)始列出全部子樹(shù) ( 除 Oracle 提供支持外 )；
????????????.返回從指定節(jié)點(diǎn)A到其子節(jié)點(diǎn)B的路徑 ( B 與 A 的層數(shù)相差會(huì)大于 1 )；
?????????DML示例：
????????????向下查找及統(tǒng)計(jì)子節(jié)點(diǎn)(僅一層)
????????????(1)通過(guò)父節(jié)點(diǎn)的ID
  通過(guò)父節(jié)點(diǎn)的ID
--查找子節(jié)點(diǎn)
select
????????*
????from
????????linkTree
????where
????????parentId?=?'01'
--統(tǒng)計(jì)子節(jié)點(diǎn)數(shù)量
select
????????parentId,
????????count(*)?'count'
????from
????????linkTree
????where
????????parentId?=?'01'
????group?by
????????parentId
????????????(2)通過(guò)父節(jié)點(diǎn)的內(nèi)容
條件列nodeContent上存在唯一性約束
--查找子節(jié)點(diǎn)
select
????????*
????from
????????linkTree
????where
????????parentId?=?(select?id?from?linkTree?where?nodeContent?=?'Root')
--統(tǒng)計(jì)子節(jié)點(diǎn)數(shù)量
select
????????parentId,
????????count(*)?'count'
????from
????????linkTree
????where
????????parentId?=?(select?id?from?linkTree?where?nodeContent?=?'Root')
????group?by
????????parentId
???????????????由于條件列無(wú)唯一性約束，所以在查找時(shí)有可能會(huì)返回一個(gè)記錄集，需要做一些處理：
條件列nodeContent上無(wú)唯一性約束
--查找子節(jié)點(diǎn),此處僅返回具有子節(jié)點(diǎn)的父節(jié)點(diǎn)行,如果需要返回全部則用right?join
select
????????sub.id?parentId,
????????main.id,
????????main.nodeContent
????from
????????linkTree?main
????????join?(select?id?from?linkTree?where?nodeContent?=?'depart1')?sub?on?main.parentId?=?sub.id
????order?by
????????parentId
--統(tǒng)計(jì)子節(jié)點(diǎn)數(shù)量,使用right?join返回全部統(tǒng)計(jì)數(shù)
select
????????parentId,
????????count(id)?'count'
????from?(
????????????select
????????????????????sub.id?parentId,
????????????????????main.id?
????????????????from
????????????????????linkTree?main
????????????????????right?join?(select?id?from?linkTree?where?nodeContent?=?'depart1')?sub?on?main.parentId?=?sub.id
????????)?tree
????group?by
????????parentId
????????????(3)查找兄弟節(jié)點(diǎn)
查找兄弟節(jié)點(diǎn)
--使用唯一列
select
????????parentId,
????????id?brotherId,
????????nodeContent
????from
????????linkTree
????where
????????parentId?=?(select?parentId?from?linkTree?where?id='01')
--使用非唯一列,由于一父多子的對(duì)應(yīng)關(guān)系,因此需要注意使用distinct關(guān)鍵字
select
????????sub.parentId,
????????main.id?brotherId,
????????main.nodeContent
????from
????????linkTree?main
????????join?(select?distinct?parentId?from?linkTree?where?nodeContent?=?'depart1')?sub?on?main.parentId?=?sub.parentId
????order?by
????????parentId
????????????(4)遍歷樹(shù)
????????????由于鏈表式表結(jié)構(gòu)的特點(diǎn)，決定了無(wú)法用通用的單次 Select 完成操作，對(duì)于不同的 DBMS 可以采取不同的策略：
????????????如Oracle
???????????????select * from linkTree start with id='00' connect by PRIOR??id = parentId;
????????????如Ms-SQL2000
???????????????表的結(jié)構(gòu)決定了數(shù)據(jù)只能逐層查找，網(wǎng)上常見(jiàn)的有使用存儲(chǔ)過(guò)程進(jìn)行遞歸返回表變量來(lái)實(shí)現(xiàn)。
???????????????這里我用循環(huán)的方式來(lái)實(shí)現(xiàn)，效率比遞歸+表變量要高。
Ms-SQL2000遍歷樹(shù)
 --這里定義的兩個(gè)參數(shù)
declare
????@level?int,????????????--查找層次
????@start?varchar(2);????--開(kāi)始節(jié)點(diǎn)ID
select?@level?=?3,@start?=?'00';????--示例賦參
--臨時(shí)變量
declare????@flag?int;????????--當(dāng)前處理節(jié)點(diǎn)的層次
declare
????@t?table?(
????????id?varchar(2),
????????nodeContent?varchar(20),
????????parentId?varchar(2),
????????site?varchar(200),
????????flag?int
????);
--初始化
set?@flag?=?1;
insert?into?@t?
????????????????select
????????????????????????linkTree.*,
????????????????????????linkTree.id,
????????????????????????@flag
????????????????????from
????????????????????????linkTree
????????????????????where
????????????????????????id?=?@start
--循環(huán)查找,結(jié)束條件(1.達(dá)到指定查找層次;2.沒(méi)有任何匹配記錄;)
while?@@rowcount?>?0?and?@flag?<?@level
begin
????set?@flag?=?@flag?+?1;????--查找層次遞增
????--向下查找匹配子節(jié)點(diǎn)
????insert?into?@t
????select
????????????linkTree.id,
????????????linkTree.nodeContent,
????????????linkTree.parentId,
????????????t.site?+?linkTree.id,
????????????@flag
????????from
?????????????@t?t?join?linkTree?on?linkTree.parentId?=?t.id
????????where
????????????t.flag?=?@flag?-?1
end
--結(jié)果輸出
select?*?from?@t?order?by?site ??????注：這里引入一個(gè)site列，是為了能夠在輸出時(shí)更加好的進(jìn)行排序，因?yàn)樵谧罱K輸出至網(wǎng)頁(yè)時(shí)很有可能是順序向下輸出的，這里正好體現(xiàn)出這種結(jié)構(gòu)。
??????b.位置描述式的結(jié)構(gòu) ( 這是藕給它起的名稱(chēng)，它有一個(gè)列，該列描述了當(dāng)前節(jié)點(diǎn)在樹(shù)中的位置)
?????????DDL示例：
create?table?siteTree?(
????id?varchar(2)?primary?key,
????nodeContent?varchar(20),
????site?varchar(200)
)
?????????例子：
????????????01??????Root??????01
????????????02??????dep1??????0102
????????????03??????dep2??????0103
????????????04??????dep11??? 010201
?????????優(yōu)點(diǎn)：
????????????由于該表結(jié)構(gòu)中帶一個(gè)位置描述列site，因此對(duì)于關(guān)于樹(shù)的遍歷等問(wèn)題的解決將變得異常的輕松。位置列按從根結(jié)點(diǎn)到當(dāng)前結(jié)點(diǎn)的ID連接而成。
?????????缺點(diǎn)：
????????????有利即有弊，位置描述列也同時(shí)突出該種結(jié)構(gòu)的缺點(diǎn)，它最大的缺點(diǎn)就是樹(shù)的層次不可能是無(wú)窮的，由于它由整個(gè)路徑的ID連接而已，因此該列的寬度并不是固定的，如根節(jié)點(diǎn)的長(zhǎng)度將是ID列的寬度，而當(dāng)前節(jié)點(diǎn)位置列所需要空間 = 當(dāng)前節(jié)點(diǎn)層次 * ID列寬度。
????????????針對(duì)鏈表式結(jié)構(gòu)的優(yōu)點(diǎn)，這里就成了缺點(diǎn)，層次間的增刪將變得較為困難。
?????????遍歷樹(shù)：
--輸出指定節(jié)點(diǎn)的全部子節(jié)點(diǎn)
select?*?from?siteTree?where?CHARINDEX('00',site)=1?order?by?site
|