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
- [[Portal Transact-SQL]]