Pesquisa de hierarquia T-SQL

Tenho uma tabela com dados hierárquicos: enter image description here

Esta é uma amostra de dados com id, ID Pai, Nome, código (que às vezes não é preenchido), nível e coluna isroot. Em cenário real haverá mais níveis do que apenas 2, mas agora vamos olhar para o exemplo simplificado.

o que preciso de fazer é percorrer todos os registos e encontrar linhas onde o id não seja preenchido a nenhum nível da hierarquia:

  • As linhas com id variando devem ser devolvidas de 6 a 10 porque eles não têm código preenchido em nenhum ponto da hierarquia
  • As linhas de 1 até 5 não devem ser devolvidas porque o código foi mencionado algures na hierarquia.
Como resolver este problema com T-SQL?

a única solução que me veio à cabeça foi a recursividade (cte ou enquanto), mas eu o que eu estava tentando implementar era muito complexo e não resolveu o problema.

Author: DhruvJoshi, 2017-12-05
Source

2 answers

Ligeiramente diferente da resposta de @DhruvJoshi, uma vez que pode ser útil: 

WITH recCTE AS
(
    SELECT 
        id, 
        parent_id, 
        CASE WHEN CODE IS NOT NULL THEN 1 ELSE 0 END as code_check,
        1 as depth,
        CAST(id as VARCHAR(50)) as path
    FROM table
    WHERE isRootLevel = 1
    UNION ALL
    SELECT
        table.id,
        table.parent_id,
        CASE WHEN CODE IS NOT NULL OR reccte.code_check = 1 THEN 1 ELSE 0 END,
        depth + 1 as depth,
        reccte.path + CASE(table.id AS varchar(10)) as path
    FROM
        recCTE 
        INNER JOIN table ON
            recCTE.ID = table.parent_id
    WHERE depth < 20 /*just in case you start cycling/endless looping*/
)
SELECT * FROM recCTE where code_check = 0 ORDER BY path, depth;
 1
Author: JNevill, 2017-12-05 18:30:39

Uma consulta como esta deve funcionar: 

; with cte as 
(
select id, parent_id,code,parent_id as RootId from tableT where IsRootLevel=1
UNION ALL
select T2.id,T2.parent_id,T2.code,T1.RootId as RootId from tableT T2 join
cte T1 on T1.id=T2.parent_id and IsRootLevel=0
)   
,
cte2 as
(select id,MAX(case when code ='' then NULL else code end) over( partition by RootId) as code from cte)

select T1.* from tableT T1 left join cte2 T2 
on T1.id=T2.id
where T2.code is NULL

ver Demonstração de trabalho

 0
Author: DhruvJoshi, 2017-12-05 18:35:24