Exemplos práticos

Para visualizar a aplicabilidade dessa técnica, serão utilizados alguns exemplos práticos no SQL Server. Através deles será possível entender porque a técnica se chama tabela ponte. A criação das tabelas necessárias, bem como os registros podem ser obtidos no script em anexo ao final do artigo. Abaixo é mostrada a hierarquia da fictícia empresa ARP Associados.

-- Selecionar todos os lançamentos do centro de custo Presidência
DECLARE @IdCentroCusto INT
SET @IdCentroCusto = (SELECT IdCentroCusto FROM CentrosCusto WHERE NomeCentroCusto = 'Presidência')

SELECT
    NomeCentroCusto, DataLancamento, ValorLancamento
FROM
    CentrosCusto AS CC
    INNER JOIN Hierarquias AS HR ON CC.IdCentroCusto = HR.IdCCInf
    INNER JOIN Lancamentos AS LAN ON HR.IdCCInf = Lan.IdCentroCusto
WHERE
    HR.IdCCSup = @IdCentroCusto And Qtd_Niv = 0

/* Observação
   O campo Qtd_Niv está igual a zero.
   Essa é a razão para a redundância de gravar o registro (1,1,0,1,0).
   Mesmo não havendo hierarquia entre o centro de custo "Presidência" e ele mesmo, isso possibilita a consulta
   Nesse exemplo a tabela ponte poderia ter sido emitida e bastaria um JOIN entre CentroCusto e Lancamento
   A utilidade da junção adicional com a tabela ponte é demonstrada nos exemplos que se seguem
*/

-- Selecionar todos os lançamentos do centro de custo Presidência e seus inferiores até o segundo nível
DECLARE @IdCentroCusto INT
SET @IdCentroCusto = (SELECT IdCentroCusto FROM CentrosCusto WHERE NomeCentroCusto = 'Presidência')

SELECT
    NomeCentroCusto, DataLancamento, ValorLancamento
FROM
    CentrosCusto AS CC
    INNER JOIN Hierarquias AS HR ON CC.IdCentroCusto = HR.IdCCInf
    INNER JOIN Lancamentos AS LAN ON HR.IdCCInf = Lan.IdCentroCusto
WHERE
    HR.IdCCSup = @IdCentroCusto And Qtd_Niv <= 2
ORDER BY
    HR.IdCCInf

-- Selecionar todos os lançamentos dos centro de custo inferiores da Presidência apenas no segundo nível
DECLARE @IdCentroCusto INT
SET @IdCentroCusto = (SELECT IdCentroCusto FROM CentrosCusto WHERE NomeCentroCusto = 'Presidência')

SELECT
    NomeCentroCusto, DataLancamento, ValorLancamento
FROM
    CentrosCusto AS CC
    INNER JOIN Hierarquias AS HR ON CC.IdCentroCusto = HR.IdCCInf
    INNER JOIN Lancamentos AS LAN ON HR.IdCCInf = Lan.IdCentroCusto
WHERE
    HR.IdCCSup = @IdCentroCusto And Qtd_Niv = 2
ORDER BY
    HR.IdCCInf

-- Selecionar todos os lançamentos dos centros de custo inferiores a Presidência imediatos ou não
DECLARE @IdCentroCusto INT
SET @IdCentroCusto = (SELECT IdCentroCusto FROM CentrosCusto WHERE NomeCentroCusto = 'Presidência')

SELECT
    NomeCentroCusto, DataLancamento, ValorLancamento
FROM
    CentrosCusto AS CC
    INNER JOIN Hierarquias AS HR ON CC.IdCentroCusto = HR.IdCCInf
    INNER JOIN Lancamentos AS LAN ON HR.IdCCInf = Lan.IdCentroCusto
WHERE
    HR.IdCCSup = @IdCentroCusto And Qtd_Niv > 0
ORDER BY
    HR.IdCCInf

-- Selecionar todos os lançamentos dos centros de custos superiores a Segurança da Informação
DECLARE @IdCentroCusto INT
SET @IdCentroCusto = (SELECT IdCentroCusto FROM CentrosCusto WHERE NomeCentroCusto = 'Segurança da Informação')

SELECT
    NomeCentroCusto, DataLancamento, ValorLancamento
FROM
    CentrosCusto AS CC
    INNER JOIN Hierarquias AS HR ON CC.IdCentroCusto = HR.IdCCSup
    INNER JOIN Lancamentos AS LAN ON HR.IdCCSup = Lan.IdCentroCusto
WHERE
    HR.IdCCInf = @IdCentroCusto
ORDER BY
    HR.IdCCInf

-- Demonstrar o caminho do centro de Custo Copa até a Presidência
DECLARE @IdCentroCusto INT
SET @IdCentroCusto = (SELECT IdCentroCusto FROM CentrosCusto WHERE NomeCentroCusto = 'Copa')

SELECT
    'Copa' AS CentroCustoInferior, NomeCentroCusto AS CentroCustoInferior, QTD_Niv AS Distancia
FROM
    CentrosCusto AS CC
    INNER JOIN Hierarquias AS HR ON CC.IdCentroCusto = HR.IdCCSup
WHERE
    HR.IdCCInf = @IdCentroCusto
ORDER BY
    QTD_Niv DESC

-- Mostrar o nó raiz
SELECT
    NomeCentroCusto AS Raiz
FROM
    CentrosCusto AS CC
    INNER JOIN Hierarquias AS HR ON CC.IdCentroCusto = HR.IdCCSup
WHERE
    HR.SIN_SUP = 1

-- Mostrar os nós intermediários
SELECT DISTINCT
    NomeCentroCusto AS Intermediarios
FROM
    CentrosCusto AS CC
    INNER JOIN Hierarquias AS HR ON CC.IdCentroCusto = HR.IdCCInf
WHERE
    HR.SIN_SUP = 0 And HR.SIN_INF = 0

-- Mostrar os nós folha
SELECT DISTINCT
    NomeCentroCusto AS Folha
FROM
    CentrosCusto AS CC
    INNER JOIN Hierarquias AS HR ON CC.IdCentroCusto = HR.IdCCInf
WHERE
    HR.SIN_INF = 1

-- Mostrar a profundidade da árvore
SELECT MAX(QTD_Niv) AS Profundidade FROM Hierarquias