T-SQL: Classificando dados em uma Tabela Hierárquica com Relação Pai-Filho

T-SQL: Classificando dados em uma Tabela Hierárquica com Relação Pai-Filho



Problema


Com base na seguinte tabela "Contas"

ContaID   Nome            PaiID
--------  -----    ------
1         Alex     0
2         João     1
3         Mateus   2
4         Felipe   1
5         Bruna    0
6         Lena     2
7         Tom      2
8         Jorge    1
9         Jim      5


a exigência é ter uma consulta que deve classificar à tabela perfeitamente com base na hierarquia pai para filho. Ou mais claramente cada filho deve estar diretamente sob seu pai. Como segue à abaixo. 

ContaID   Nome            PaiID
--------  -----    ------
1         Alex     0
8         Jorge    1
2         João     1
3         Mateus   2
6         Lena     2
7         Tom      2
4         Felipe   1
5         Bruna    0
9         Jim      5



Pense nisso como uma busca em profundidade, onde os filhos são classificados em ordem alfabética.
Ir tão longe para baixo o ramo mais à esquerda como você pode, então passar um ramo para a direita. Então os filhos de John ter coletados antes de continuar listando os filhos de Alex.

Solução


This uses a recursive cte to build the hierarchy, and each level, orders by nome. If you leave the [path] column in the final select, you will see how it has been built up, and this is used to order the final result set.

declare @Contas table (ContaID int, Nome  varchar(50), PaiID int)
   
insert into @Contas select 1,'Alex',0
insert into @Contas select 2,'João',1
insert into @Contas select 3,'Mateus',2
insert into @Contas select 4,'Felipe',1
insert into @Contas select 5,'Bruna',0
insert into @Contas select 6,'Lena',2
insert into @Contas select 7,'Tom',2
insert into @Contas select 8,'Jorge',1
insert into @Contas select 9,'Jim',5
  
   
;with cte as
(
select
    ContaID,
    nome,
    PaiId,
    cast(row_number()over(partition by PaiId order by nome) as varchar(max)) as  Caminho,
    0 as level,
    row_number()over(partition by PaiId order by nome) / power(10.0,0) as  x
 
from @Contas
where PaiId = 0
union all
select
    t.ContaID,
    t.nome,
    t.PaiId,
    Caminho +'-'+ cast(row_number()over(partition by t.PaiId order by t.nome) as varchar(max)),
    level+1,
    x + row_number()over(partition by t.PaiId order by t.nome) / power(10.0,level+1)
 
from
    cte
join  @Contas t on cte.ContaID = t.PaiId
)
   
select
    ContaID,
    nome,
    PaiId,
    Caminho,
    x
from cte
order by x


isto resulta em:

ContaID     nome              PaiId       Caminho   x
---------   --------- --------    -------  --------------------
1           Alex      0           1        1.000000000000000000
8           Jorge     1           1-1      1.100000000000000000
2           João      1           1-2      1.200000000000000000
3           Mateus    2           1-2-1    1.210000000000000000
6           Lena      2           1-2-2    1.220000000000000000
7           Tom       2           1-2-3    1.230000000000000000
4           Felipe    1           1-3      1.300000000000000000
5           Bruna     0           2        2.000000000000000000
9           Jim       5           2-1      2.100000000000000000


A coluna "Caminho" indica o nível em que a conta esta na hierarquia, assim por exemplo "Lena" é  1-2-2, lendo da direita para à esquerda isto significa que ela é o segundo filho do segundo filho do primeiro pai, ou em outras palavras, ela é o segundo filho do segundo filho de Alex => ou melhor, é o segundo filho de João.

Veja Também


Outros Idiomas


Classificar por: Data da Publicação | Mais Recente | Mais Úteis
Comentários
  • Sorry, but Please go read the original WIKI's comments. This code is not working!

    * Try to change the name on record 8 into George and then add these records and check the results:

    insert into @Contas select 10,'Jim',8

    insert into @Contas select 11,'Jim',8

    insert into @Contas select 12,'Jim',8

    insert into @Contas select 13,'Jim',8

    insert into @Contas select 14,'Jim',8

    insert into @Contas select 15,'Jim',8

    insert into @Contas select 16,'Jim',8

    insert into @Contas select 17,'Jim',8

    insert into @Contas select 18,'Jim',8

    insert into @Contas select 19,'Jim',8

    insert into @Contas select 20,'Jim',8

    insert into @Contas select 21,'Jim',8

    insert into @Contas select 22,'Jim',8

    insert into @Contas select 23,'Jim',8

  • Pituach,

    Thanks for pointing the problem (which is now mine too). I did the translation because it seemed an interesting solution and was tagged "TechNet Guru", which to me is proof that the author believe on the solution that this disclosing.

    If the author does not correct the original article soon, I'll use your tip to fix this Article

Página 1 de 1 (2 itens)