Início / dba / Perguntas / 4462
Accepted
Richard
Asked: 2011-08-09 05:43:51 +0800 CST

Encontre todos os nós ancestrais de um HierarchyId usando uma única instrução SQL?

  • 772

Estou tentando encontrar uma maneira de obter todos os nós ancestrais de um determinado nó usando HierarchyID. Todas as soluções que vi usando HierarchyID parecem usar um CTE ou uma variável. Existe uma maneira de fazer isso usando uma única instrução select?

Para tornar as coisas mais simples:

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 respostas

  1. Best Answer

    Para obter "todos os nós pais de um determinado nó":

    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/

    mas sempre haverá apenas um devido à natureza das hierarquias.

    Se você realmente deseja obter todos os nós filhos imediatos de um determinado nó:

    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

    -- EDITAR

    Vejo que você deseja todos os nós ancestrais. Talvez tente uma abordagem como esta:

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

    ou

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

    aqui está um método CTE para comparação:

    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

relate perguntas