樹形結構的數據庫表Schema設計

來源：懂視網責編：小采時間：2020-11-09 08:05:03

樹形結構的數據庫表Schema設計

樹形結構的數據庫表Schema設計:那么某個節點到底有多少的子孫節點呢？通過該節點的左、右我們可以將其子孫節點圈進來，則子孫總數 = (右 – 左– 1) / 2，以Fruit為例，其子孫總數為：(11 –2 – 1) / 2 = 4。同時，為了更為直觀地展現樹形結構，我們需要知道節點在樹中所處的層次，通過左

推薦度：

點擊下載本文 文檔為doc格式

導讀樹形結構的數據庫表Schema設計:那么某個節點到底有多少的子孫節點呢？通過該節點的左、右我們可以將其子孫節點圈進來，則子孫總數 = (右 – 左– 1) / 2，以Fruit為例，其子孫總數為：(11 –2 – 1) / 2 = 4。同時，為了更為直觀地展現樹形結構，我們需要知道節點在樹中所處的層次，通過左

那么某個節點到底有多少的子孫節點呢？通過該節點的左、右我們可以將其子孫節點圈進來，則子孫總數 = (右 – 左– 1) / 2，以Fruit為例，其子孫總數為：(11 –2 – 1) / 2 = 4。同時，為了更為直觀地展現樹形結構，我們需要知道節點在樹中所處的層次，通過左

那么某個節點到底有多少的子孫節點呢？通過該節點的左、右值我們可以將其子孫節點圈進來，則子孫總數 = (右值 – 左值– 1) / 2，以Fruit為例，其子孫總數為：(11 –2 – 1) / 2 = 4。同時，為了更為直觀地展現樹形結構，我們需要知道節點在樹中所處的層次，通過左、右值的SQL查詢即可實現，以Fruit為例：SELECTCOUNT(*) FROM Tree WHERE Lft <= 2 AND Rgt >=11。為了方便描述，我們可以為Tree建立一個視圖，添加一個層次數列，該列數值可以寫一個自定義函數來計算，函數定義如下：

CREATE FUNCTION dbo.CountLayer
(
 @node_id int
)
RETURNS int
AS
begin
	declare @result int
	set @result = 0
	declare @lft int
	declare @rgt int
	if exists(select Node_id from Tree where Node_id = @node_id)
	begin
	select @lft = Lft, @rgt = Rgt from Tree where node_id = @node_id
	select @result = count(*) from Tree where Lft <= @lft and Rgt >= @rgt
	end
	return @result
end
GO

基于層次計算函數，我們創建一個視圖，添加了新的記錄節點層次的數列：

CREATE VIEW dbo.TreeView
AS
SELECT Node_id, Name, Lft, Rgt, dbo.CountLayer(Node_id) AS Layer FROM dbo.Tree ORDER BY Lft
GO

創建存儲過程，用于計算給定節點的所有子孫節點及相應的層次：

CREATE PROCEDURE [dbo].[GetChildrenNodeList]
(
	@node_id int
)
AS
declare @lft int
declare @rgt int
if exists(select Node_id from Tree where node_id = @node_id)
	begin
	select @lft = Lft, @rgt = Rgt from Tree where Node_id = @node_id
	select * from TreeView where Lft between @lft and @rgt order by Lft ASC
	end
GO

現在，我們使用上面的存儲過程來計算節點Fruit所有子孫節點及對應層次，查詢結果如下：

從上面的實現中，我們可以看出采用左右值編碼的設計方案，在進行樹的查詢遍歷時，只需要進行2次數據庫查詢，消除了遞歸，再加上查詢條件都是數字的比較，查詢的效率是極高的，隨著樹規模的不斷擴大，基于左右值編碼的設計方案將比傳統的遞歸方案查詢效率提高更多。當然，前面我們只給出了一個簡單的獲取節點子孫的算法，真正地使用這棵樹我們需要實現插入、刪除同層平移節點等功能。

（2）獲取某節點的族譜路徑

假定我們要獲得某節點的族譜路徑，則根據左、右值分析只需要一條SQL語句即可完成，以Fruit為例：SELECT* FROM Tree WHERE Lft < 2 AND Rgt > 11 ORDER BY Lft ASC ，相對完整的存儲過程：

CREATE PROCEDURE [dbo].[GetParentNodePath]
(
	@node_id int
)
AS
declare @lft int
declare @rgt int
if exists(select Node_id from Tree where Node_id = @node_id)
	begin
	select @lft = Lft, @rgt = Rgt from Tree where Node_id = @node_id
	select * from TreeView where Lft < @lft and Rgt > @rgt order by Lft ASC
	end
GO

（3）為某節點添加子孫節點

假定我們要在節點“Red”下添加一個新的子節點“Apple”，該樹將變成如下圖所示，其中紅色節點為新增節點。

仔細觀察圖中節點左右值變化，相信大家都應該能夠推斷出如何寫SQL腳本了吧。我們可以給出相對完整的插入子節點的存儲過程：

CREATE PROCEDURE [dbo].[AddSubNode]
(
	@node_id int,
	@node_name varchar(50)
)
AS
declare @rgt int
if exists(select Node_id from Tree where Node_id = @node_id)
	begin
	SET XACT_ABORT ON
	BEGIN TRANSCTION
	select @rgt = Rgt from Tree where Node_id = @node_id
	update Tree set Rgt = Rgt + 2 where Rgt >= @rgt
	update Tree set Lft = Lft + 2 where Lft >= @rgt
	insert into Tree(Name, Lft, Rgt) values(@node_name, @rgt, @rgt + 1)
	COMMIT TRANSACTION
	SET XACT_ABORT OFF
	end
GO

（4）刪除某節點

如果我們想要刪除某個節點，會同時刪除該節點的所有子孫節點，而這些被刪除的節點的個數為：(被刪除節點的右值 – 被刪除節點的左值+ 1) / 2，而剩下的節點左、右值在大于被刪除節點左、右值的情況下會進行調整。來看看樹會發生什么變化，以Beef為例，刪除效果如下圖所示。

則我們可以構造出相應的存儲過程：

CREATE PROCEDURE [dbo].[DelNode]
(
	@node_id int
)
AS
declare @lft int
declare @rgt int
if exists(select Node_id from Tree where Node_id = @node_id)
	begin
	SET XACT_ABORT ON
	BEGIN TRANSCTION
	select @lft = Lft, @rgt = Rgt from Tree where Node_id = @node_id
	delete from Tree where Lft >= @lft and Rgt <= @rgt
	update Tree set Lft = Lft – (@rgt - @lft + 1) where Lft > @lft
	update Tree set Rgt = Rgt – (@rgt - @lft + 1) where Rgt > @rgt
	COMMIT TRANSACTION
	SET XACT_ABORT OFF
	end
GO

五、總結

我們可以對這種通過左右值編碼實現無限分組的樹形結構Schema設計方案做一個總結：

（1）優點：在消除了遞歸操作的前提下實現了無限分組，而且查詢條件是基于整形數字的比較，效率很高。

（2）缺點：節點的添加、刪除及修改代價較大，將會涉及到表中多方面數據的改動。

當然，本文只給出了幾種比較常見的CRUD算法的實現，我們同樣可以自己添加諸如同層節點平移、節點下移、節點上移等操作。有興趣的朋友可以自己動手編碼實現一下，這里不在列舉了。值得注意的是，實現這些算法可能會比較麻煩，會涉及到很多條update語句的順序執行，如果順序調度考慮不周詳，出現Bug的話將會對整個樹形結構表產生驚人的破壞。因此，在對樹形結構進行大規模修改的時候，可以采用臨時表做中介，以降低代碼的復雜度，同時，強烈推薦在做修改之前對表進行完整備份，以備不時之需。在以查詢為主的絕大多數基于數據庫的應用系統中，該方案相比傳統的由父子繼承關系構建的數據庫Schema更為適用。

參考文獻：《Storing Hierarchical Data in a Database Article》

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com

樹形結構的數據庫表Schema設計

推薦度：

點擊下載本文 文檔為doc格式

標簽：設計數據庫某個

熱門焦點

国产99久久精品_欧美日本韩国一区二区_激情小说综合网_欧美一级二级视频_午夜av电影_日本久久精品视频

樹形結構的數據庫表Schema設計

樹形結構的數據庫表Schema設計

樹形結構的數據庫表Schema設計

最新推薦

猜你喜歡

熱門推薦