使用單個 SQL 語句查找 HierarchyId 的所有祖先節點？

我正在嘗試找到一種方法來使用 HierarchyID 獲取給定節點的所有祖先節點。我見過的每個使用 HierarchyID 的解決方案似乎都使​​用 CTE 或變數。有沒有辦法使用單個選擇語句來做到這一點？

為了讓事情更簡單：

CREATE TABLE Employee
(
   EmpId INT PRIMARY KEY IDENTITY,
   EmpName VARCHAR(100) NOT NULL,
   Position HierarchyID NOT NULL
)

INSERT INTO Employee (EmpName, Position)
VALUES ('CEO', '/'),
   ('COO', '/1/'),
   ('CIO', '/2/'),
   ('CFO', '/3/'),
   ('VP Financing', '/3/1/'),
   ('Accounts Receivable', '/3/1/1/'),
   ('Accountant 1', '/3/1/1/1/'),
   ('Accountant 2', '/3/1/1/2/'),
   ('Accountant 3', '/3/1/1/3/'),
   ('Accounts Payable', '/3/1/2/'),
   ('Accountant 4', '/3/1/2/1/'),
   ('Accountant 5', '/3/1/2/2/'),
   ('DBA', '/2/1/'),
   ('VP of Operations', '/1/1/')

要獲取“給定節點的所有父節點”：

select *, position.GetAncestor(1), position.GetAncestor(1).ToString()
from employee
where position=hierarchyid::Parse('/3/1/')

EmpId  EmpName       Position  (No column name)  (No column name)
5      VP Financing  0x7AC0    0x78              /3/

但由於層次結構的性質，只會有一個。

如果您真的想獲取給定節點的所有直接子節點：

select * 
from employee 
where position.IsDescendantOf(hierarchyid::Parse('/3/1/'))=1
     and position.GetLevel()=hierarchyid::Parse('/3/1/').GetLevel()+1

EmpId  EmpName              Position
6      Accounts Receivable  0x7AD6
10     Accounts Payable     0x7ADA

• 編輯

我看到您想要所有祖先節點。也許嘗試這樣的方法：

select * 
from employee
where hierarchyid::Parse('/3/1/2/1/').IsDescendantOf(Position) = 1

要麼

select * from employee
where ( select position 
       from employee 
       where empname='Accountant 4' ).IsDescendantOf(Position) = 1

這是一個CTE方法進行比較：

with w as ( select * from employee where empname='Accountant 4'
           union all
           select e.*
           from employee e join w on(w.position.GetAncestor(1)=e.Position) )
select * from w;

引用自：https://dba.stackexchange.com/questions/4462