HIERARCHYID的基本應用

--建立TABLE Employees
CREATE TABLE Employees
(
Org_Id HIERARCHYID NOT NULL,
EmployeeId INT NOT NULL,
EmployeeName VARCHAR(50) NOT NULL,
Title VARCHAR(50) NOT NULL
)
GO
--輸入一些資料，必須用/開始和结束。這是路径的符号。此為關鍵键
INSERT INTO dbo.Employees VALUES('/',10000,'帥哥劉','CEO');
INSERT INTO dbo.Employees VALUES('/1/',10001,'張三','CTO');
INSERT INTO dbo.Employees VALUES('/2/',10002,'李四','CFO');
INSERT INTO dbo.Employees VALUES('/1/1/',10003,'王五','IT Manager');
INSERT INTO dbo.Employees VALUES('/1/2/',10004,'趙六','Manager');
INSERT INTO dbo.Employees VALUES('/1/1/1/',10005,'洪七','Employee');
--查看所有的員工工
SELECT * FROM dbo.Employees
GO
--呼叫 .NET 屬性、方法時，大小寫有別
SELECT *
,Org_Id.ToString() AS 實際資料
,Org_Id.GetLevel() AS 資料層級
,Org_Id.GetAncestor(0).ToString() AS 取得本層
,Org_Id.GetAncestor(1).ToString() AS 取得上一層
,Org_Id.GetAncestor(2).ToString() AS 取得上二層
,HIERARCHYID::GetRoot().ToString() AS 取得根節點
,Org_Id.GetDescendant(NULL,NULL).ToString() AS 取得下一個節點
,Org_Id.IsDescendantOf('/1/') AS '/1/是否為其祖先'
FROM Employees
GO











--資料型態為hierarchyid的變數也可以使用屬性、方法
DECLARE @Employee HIERARCHYID
SELECT @Employee=Org_Id FROM Employees WHERE EmployeeID=10004
SELECT *
,@Employee.ToString() AS 變數實際資料
,@Employee.GetLevel()AS 變數層級
FROM Employees WHERE @Employee.IsDescendantOf(Org_Id)=1
--參考網址
--http://tech.ddvip.com/2009-09/1253374581133588.html
--http://thoughtofgenius.blogspot.com/2008/11/sql-server-2008-hierarchyid.html