SQL Server 2008 w/ hierarchyId
鉴于这个结构:
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 *,
(select count(*) from dbo.Employee e where Position.GetAncestor(1) = Employee.Position)
as DirectDescendantsCount
from
dbo.Employee
结果:
EmpId | EmpName | Position | DirectDescendantsCount
1 CEO 0x 3
2 COO 0x58 1
3 CIO 0x68 1
4 CFO 0x78 1
5 VP Financing 0x7AC0 2
6 Accounts Receivable 0x7AD6 3
7 Accountant 1 0x7AD6B0 0
8 Accountant 2 0x7AD6D0 0
9 Accountant 3 0x7AD6F0 0
10 Accounts Payable 0x7ADA 2
11 Accountant 4 0x7ADAB0 0
12 Accountant 5 0x7ADAD0 0
13 DBA 0x6AC0 0
14 VP of Operations 0x5AC0 0
这种类型的操作在上有哪些更有效的查询方式?
感谢您的任何帮助!