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