sql - 使用 HierarchyId 获取直接后代计数

Question

SQL Server 2008 带 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

在 hierarchyid 上进行此类操作的更有效查询是什么？ ?

谢谢你的帮助!

Answer 1

您可以像这样消除子查询:

SELECT  A.EmpId, A.EmpName, A.Position, COUNT(B.EmpId) AS DirectDescendantsCount
FROM    Employee AS A 
        LEFT OUTER JOIN Employee AS B
            ON A.Position = B.Position.GetAncestor(1)
group by A.EmpId, a.EmpName, A.Position