SQL Story（十一）－－樹狀表遊戲 (轉)

SQL Story（十一）－－樹狀表遊戲 (轉)[@more@]

樹狀結構的與管理，是每一個在關係型平臺上工作的員早晚都要遇到的問題。說大不大，怎麼都能解決，說小不小，處理不好，有的是麻煩等著你。仁者見仁，智者見智，公說公有理，婆說婆有理（誰用機箱砸我？機箱是個好東西，亂丟會摔壞的，你看我話沒說完你又把顯示器丟了……），咳咳，好吧，閒話少說，我們從最大路的處理風格談一談吧。這裡面的大部分內容並非我的獨創，從很久很久以前，資料庫程式設計師們就這樣做啦。:namespace prefix = o ns = "urn:schemas--com::office" />

樹狀表的結構化表達

  在一切開始前，我們先就樹狀表的表示方式達成一個共識。在關係型資料庫中，我們當然沒有辦法這樣直接表示一個樹：

a

b  c

d e f g

  相應的，我們會把它變形為平面表，這種變形讓我想起拓撲幾何：

r  n1  n2

a  b  d

a  b  e

a  c  f

a  c  g

儲存結構

稍有的朋友，大概都不會試著把樹狀結構一層一列的存進去了吧。這樣做的問題是顯而易見的：與表中儲存的資訊結構不同，表的結構應該是相對固定的，不能隨便改動。而對於層數不能固定的普通樹狀結構，這是不可思議的。沒有必要討論過多的錯誤，我們選擇一個相對正確的方式――把樹狀結構抽象成適合關聯式資料庫的形式。

只考慮某一個節點的話，和這個節點相關的資訊是：它的唯一標識、父節點、子節點、資料資訊。這其中只有子節點的數目不定。不過如果每一個節點都確定了自己的父節點，顯然可以省略子節點。這樣一來，一個節點需要儲存三部分資訊――它的唯一標識、父節點、資料資訊。一個理想的TreeView表只需要三個節點就可以了，用語句來表達就是

CREATE TABLE [o].[TreeView] (

  [ID] [char] (10) PRIMARY KEY,

  [PID] [char] (10) FOREIGN KEY REFERENCES [dbo].[TreeView],

  [DATA] [char] (10) 

)

  ID是當前節點的唯一標識號，顯然它應當是主鍵；我們建立的是一個自閉的儲存結構，每一個節點的父節點也應當出自表中儲存的節點，所以PID列引用ID作為外來鍵；至於DATA，它是節點中的資訊，通常和樹的結構沒有絕對關係。我把這三列全設成Char(10)，是為了後面做演示更方便，當然也有人喜歡用自動標識列來做主鍵，在這種場合，也自有其優點。為了維護資料的完整性或儲存、檢索等方面的考慮，實用中我們可能會採用更復雜的結構，不過骨幹就這樣子了。這個結構從數學上講很簡潔，而且是自洽的。如果一個節點沒有父節點，那麼它的PID就等於它自己的ID。這並不違反我們關於主外來鍵的定義。

資訊的管理與使用

  樹表的結構確定後，問題就集中在如何讀寫其中的資料。

  增加節點：增加一個葉節點很簡單，只要指定這個節點的父節點，把它“掛接”到TreeView中。從遞迴的角度來看，我們可以重複這一步驟，真到插入一個完整的子樹。相對而言，比較麻煩的是，如何把一個子節點插入到現有的樹中間，而不是最末端。比如存在一個節點N，它的根為R，現在要在R和N之間插入一個新節點N’，我們可以這樣做：把N’掛在R下面，作為它的子節點，然後把N的父節點指定為N’即可。

  修改節點：這裡指修改樹結構，改變某一節點的父節點，在這種結構中，修改是很方便的，只要標準的update就可以。

  刪除節點：刪除節點時要注意這個節點下面還有沒有子節點，如果有，我們通常以兩種方式處理，一是把相關子節點全刪掉，如果是MS 2000這樣的，你可以很簡單的在建表時將外來鍵指定為支援級聯刪除，自己寫一個級聯刪除比較麻煩，不過也不是不可能，重點在於，為這個過程建立遞迴。簡要示例如下：

--定義

CREATE PROCEDURE DeleteNode

  @NodeID Char(10)

AS

BEGIN

  --以當前節點的子節點作為記錄集建立遊標

  DECLARE ChildNodes CURSOR

  READ_ONLY

  FOR ID FROM TreeView WHERE PID = @NodeID

  DECLARE @ChildNode VARCHAR(40)

  OPEN ChildNodes

  FETCH NEXT FROM ChildNodes INTO @ChildNode

  WHILE (@@fetch_status <> -1)--判斷記錄集是否成功開啟

  BEGIN

  IF (@@fetch_status <> -2)

  BEGIN

  --遞迴呼叫

  EXEC DeleteNode @ChildNode

  END

  FETCH NEXT FROM ChildNodes INTO @ChildNode

  END

  CLOSE ChildNodes

  DEALLOCATE ChildNodes

  --程式碼到這裡，可以確定@NodeID不再有子節點，現在，我們刪除它

  DELETE FROM TreeView WHERE ID = @NodeID

END;

當然，這是一種比較低效的設計，每一個將要刪除的節點都要執行一次Delete，比較有的方法是多深入一層，操作當前節點的子節點。有興趣的讀者可以一試。

  查詢：樹狀表的查詢是最有趣的內容之一。當然僅僅查出某一個節點的資訊沒有太大的意思，我們希望的是得到從當前節點一直向下（通常是到最底層）的完整子樹。這同樣需要一個遞迴。讓我們從一個歸納法遊戲開始，事先，我們先在TreeView中插入以下資料：

ID

PID

DATA

----------

----------

----------

ROOT

ROOT

N11

ROOT

Node1-1

N12

ROOT

Node1-2

N13

ROOT

Node1-3

N21

N11

Node2-1

N22

N11

Node2-2

N23

N12

Node2-3

N24

N13

Node2-4

N31

N21

Node3-1

N32

N21

Node3-2

N33

N21

Node3-3

N34

N22

Node3-4

  歸納法第一步，當然是構造初值，查詢子樹的根節點就是標準的SQL，SELECT ID, DATA FOTreeView WHERE ID = …，在這裡有一個細節，查詢表中所有的根節點（細心的讀者可能早就發現，我們設計的這個TreeView可以儲存任意多個樹）可以透過SELECT R.ID, R.DATA FORM TreeView R WHERE R.ID = R.PID來實現。簡單起見，我們就從這裡起步吧。

  第二步，選擇出前兩層也不難，

SELECT R.ID, N1.ID, N1.DATA

FROM TreeView R

LEFT JOIN TreeView N1

ON R.ID = N1.PID

AND R.ID <> N1.ID

WHERE R.ID = R.PID

  我們要注意的是加藍的部分，這是增加一層後做改動的部分。

  很容易，我們得到前三層，

SELECT R.ID, N1.ID, N2.ID, N2.DATA

FROM TreeView R

LEFT JOIN TreeView N1

ON R.ID = N1.PID

AND R.ID <> N1.ID

LEFT JOIN TreeView N2

ON N1.ID = N2.PID

AND N1.ID <> N2.ID

WHERE R.ID = R.PID

  同樣的，我們重點觀察加藍的部分。相信經過這兩步，大家會發現其中的規律。現在我們可以把這個過程自動化了……

  ……

  不知道大家是否還記得前幾集裡那個關於四色問題的，我又一次犯了這樣的錯誤。一個多月以來，我就陷在這裡不能自拔。顯然，我低估了問題的難度。這個問題似乎不能轉化為一個簡單，只能透過一個遞迴的流程來實現。而流程化的程式又非SQL所長。這裡面有著各種各樣的麻煩。相信試過的朋友都有體會。現在我使用的架構問題是顯然的，它需要明確知道到底從子樹的頂點向下到底有多少層。所以，計算樹的層數就首先被提了出來。

  但是，但是，但是……（我簡直沒臉見人啦）我一直找不到一個簡潔優雅的方法。一個多月的時間就這麼過去了。我不得不一點點放棄我的原則（此乃人生墮落之始啊）。最後，我得承認，要想寫出一個優雅的樹狀表選擇來，對我還是比較困難的。所以，在這一篇文章裡，我們先討論到這裡，樹狀表的選擇――其實應該說樹狀檢視構造，還是留到下次再討論吧。