主页 / dba / 问题 / 4462
Accepted
Richard
Asked: 2011-08-09 05:43:51 +0800 CST

使用单个 SQL 语句查找 HierarchyId 的所有祖先节点？

  • 772

我正在尝试找到一种方法来使用 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/')
sql-server-2008 t-sql

2 个回答

  1. Best Answer

    要获取“给定节点的所有父节点”：

    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;
    • 10 
  2. DECLARE @hid_Specific HIERARCHYID 

/** Assign your specific node path **/
SET @hid_Specific='/1/1/3/1/';

SELECT 
     hrchy_id,
     * 
FROM 
     tblHierarchyData 
WHERE 
     PATINDEX(hrchy_id.ToString()+'%',@hid_Specific.ToString())=1
    • 0

相关问题