Aqueles que tem problemas em relacionar dados em um banco de dados onde há uma amarração por meio de árvore, segue uma boa dica de como utilizar querys recursivas no SQL Server.
Antes de mais nada vamos preparar a tabela que utilizaremos em nosso exemplo:
CREATE TABLE Hierarquia (
IdHierarquia INT PRIMARY KEY,
IdHierarquiaPai INT
)
INSERT INTO Hierarquia VALUES (1,NULL);
INSERT INTO Hierarquia VALUES (2,4);
INSERT INTO Hierarquia VALUES (3,1);
INSERT INTO Hierarquia VALUES (4,1);
INSERT INTO Hierarquia VALUES (5,2);
INSERT INTO Hierarquia VALUES (6,4);
INSERT INTO Hierarquia VALUES (7,3);
INSERT INTO Hierarquia VALUES (8,1);
INSERT INTO Hierarquia VALUES (9,2);
INSERT INTO Hierarquia VALUES (10,1);
INSERT INTO Hierarquia VALUES (11,4);
INSERT INTO Hierarquia VALUES (12,2);
INSERT INTO Hierarquia VALUES (13,3);
INSERT INTO Hierarquia VALUES (14,1);
INSERT INTO Hierarquia VALUES (15,4);
INSERT INTO Hierarquia VALUES (16,3);
INSERT INTO Hierarquia VALUES (17,2);
INSERT INTO Hierarquia VALUES (18,2);
INSERT INTO Hierarquia VALUES (19,1);
INSERT INTO Hierarquia VALUES (20,1);
Criada a tabela e inserida seu elementos vamos a nossa query de recursividade:
WITH Filhos(IdHierarquia, IdHierarquiaPai, nivel, coluna)
AS
(
-- Membro Ancora
SELECT
IdHierarquia,
IdHierarquiaPai,
0,
CAST(IdHierarquia AS BINARY(900))
FROM Hierarquia
UNION ALL
-- Membros Recursivos
SELECT
H.IdHierarquia,
H.IdHierarquiaPai,
F.nivel + 1,
CAST(coluna + CAST(H.IdHierarquia AS BINARY(10)) AS BINARY(900))
FROM Hierarquia H
INNER JOIN Filhos F ON H.IdHierarquiaPai = F.IdHierarquia
)
-- Query com Resultado
SELECT
IdHierarquiaPai,
IdHierarquia,
REPLICATE('| ' , nivel)
+ (CAST(IdHierarquia AS VARCHAR(20))) AS [Arvore de Hierarquia]
FROM Filhos
ORDER BY coluna
Vamos entender oque foi feito.
1º A utlização do WITH vem de um dos novos recursos atribuídos ao SQL Server 2005, onde é utilizado para criação de views temporárias, válida somente dentro do seu batch atual. Esse recurso é denomindado de Common Table Expressions(CTE).
Sintaxe:
WITH [name] AS (
[instrução SELECT]
)
2º A utilização de UNION ALL separando as querys a serem utilizadas, sendo que a primeira é a própria view criada a partir do primeiro elemento de comparação, no nosso caso:
-- Membro Ancora
SELECT
IdHierarquia,
IdHierarquiaPai,
0,
CAST(IdHierarquia AS BINARY(900))
FROM Hierarquia
O primeiro SELECT é denominado de membro âncora, definindo o ponto de entrada da hirarquia.
O segundo SELECT é denominado de membros recursivos onde é incluído o CTE criado em JOIN com a tabela a ser buscado os elementos:
-- Membros Recursivos
SELECT
H.IdHierarquia,
H.IdHierarquiaPai,
F.nivel + 1,
CAST(coluna + CAST(H.IdHierarquia AS BINARY(10)) AS BINARY(900))
FROM Hierarquia H
INNER JOIN Filhos F ON H.IdHierarquiaPai = F.IdHierarquia
Acho que valeu a dica.
Bom Divertimento!
referências:
Linha de Código
MSDN
