原木刻痕

前言

有過一點系統經驗的人應該對於樹狀表不陌生，不管是功能節點，權限節點，會計分帳，組織關係基本上資料表結構都會是樹狀的，就是會有一個ParentNode，使其可以追蹤成為一個完整的動態資料結構。但說實話，每次查詢的語法既複雜又耗時又令人感覺不是很好，因此分享處理訣竅。

 

概念說明

基本上樹狀結構麻煩的地方就是在於處理會是遞迴但你也知道SQL畢竟不是程式，因此遞迴處理不僅有限制，跑起來效能也不會很好，更重要的是"寫出來的東西第一次很容易炸掉"。因此針對這種東西要改變思考點，應該是"我如何找到該點於樹狀的位置(先不管樹圖產生沒)"，接著就可以思考，如何不用遞迴參考樹圖。然後就可以得到"我將先前走過一次的路徑記錄下來不就好了?"這樣的結論。沒錯，樹圖這種東西只要於特定時段或特定時機更新維持基本的正確性即可，不需要每次都尋訪一次。

 

實際動手做

首先準備一個簡單的樹狀資料表

接著增加一個欄位叫做Path(概念上的意思)

接著就是走訪一次樹，並將過程記錄下來，作為樹圖。語法大概長得像這樣(利用CTE走遞迴，也可以用WHILE無限迴圈走，我是習慣用CTE)

WITH tmpTree AS (
　　--取得根節點
　　SELECT 
		[Id],
		[ParentId],
		[Name],
		Convert(nvarchar(150),CONCAT('.',[Id],'.')) as [Path]
	FROM Tree  
	WHERE ParentId IS NULL
　UNION All
　　SELECT 
		DataSource.[Id],
		DataSource.[ParentId],
		DataSource.[Name],
		Convert(nvarchar(150),CONCAT(TreeNode.Path , '->','.',DataSource.[Id],'.')) as Path
　　FROM Tree DataSource 
	JOIN tmpTree TreeNode 
		ON	DataSource.ParentId=TreeNode.Id
		AND	TreeNode.Path NOT LIKE '%'+CONCAT('.',DataSource.Id,'.')+'%' --避免有繞圈圈的資料
)
SELECT * FROM tmpTree

輸出結果長這樣

然後放到資料欄位中，一份現有的樹圖就跑好囉，往後就再也不用每次查詢每次遞迴了，可喜可賀。

PS:用點當作封裝符號純屬個人喜好(括弧會讓往後查詢變麻煩)

 

後語

1.更新時機可以放在TRIGGER，每次改變ParentId時走一次遞迴(單節點往根走，負擔低)

2.也可以放在SQL Agent的排程每日更新確保資料正確