none
RECURSIVIDADE - CTE (COMMON TABLE EXPRESSION) RRS feed

  • Pergunta

  • Pessoal, estou precisando de uma ajuda espero contar com vc's, é o seguinte preciso criei uma tabela que irar formar a hierarquia organizacional da empresa e os niveis abaixo abaixo podem ser infinitos dai criei uma tabela que se auto relaciona portanto vou precisa ultilizar recursividade para descer ate o ultimo nivel ate ai deu certo. O meu problema é que preciso somar o campo valor para os niveis superiores ou seja no exemplos do gerente financeiro o valor dele vai ser o valor cadastrado na tabela mais o valor de todos niveis inferiores e assim sucessivamente.

    Presidente
    Gerente Financeiro
    Coordenador
    Supervisor
    etc...
    Gerente de Suoprimentos
    Coordenador de Suprimentos
    Supervisor de Suprimentos
    Comprador
    etc...


     ESTRUTURA DA TABELA

    CREATE TABLE [dbo].[Pessoas](
        [Id_Pessoa] [int] NULL,
        [Nome] [varchar](50) NULL,
        [Cargo] [varchar](50) NULL,
        [Nivel] [tinyint] NULL,
        [Id_Gerente] [int] NULL,
        [Valor] [int] NULL
    ) ON [PRIMARY]

    GO

    RECURSIVIDADE - CTE (COMMON TABLE EXPRESSION)
    ==============================================
    WITH Relatorio (Id_Pessoax, NivelRec, Nome, Caminho) AS
    (
    SELECT Id_Pessoa,1, CAST(Nome AS VARCHAR(MAX)), CAST(Nome AS VARCHAR(MAX))
    From Pessoas
    WHERE Id_Gerente IS NULL
    UNION ALL
    SELECT P.Id_Pessoa, NivelRec + 1,
        CAST(SPACE((NivelRec) * 5) + P.Nome AS VARCHAR(MAX)),
        Caminho + P.Nome
    FROM Pessoas P
    INNER JOIN Relatorio Rel ON P.Id_Gerente = Id_Pessoax
    )

    SELECT * FROM Relatorio
    ORDER BY Caminho, Nome




    Obrigado
    Charbel Daia






      
    terça-feira, 14 de outubro de 2008 20:03

Respostas

  • Olá Charbel,

     

    Essa deu um trabalhinho... O segredo era inverter a ordem. Ao invés de descer do topo à base, fui da base ao topo.

    Segue o exemplo:

     

    Code Snippet

    CREATE TABLE tblFuncionarios (

    IDFuncionario INT,

    NomeFuncionario VARCHAR(50),

    IDSuperior INT NULL,

    ValorVendido SMALLMONEY)

     

    -- Insere os registros

    INSERT INTO tblFuncionarios VALUES (1,'Thelma',NULL,350.00)

    INSERT INTO tblFuncionarios VALUES (2,'Denise',1,250.00)

    INSERT INTO tblFuncionarios VALUES (3,'Tamara',2,200.00)

    INSERT INTO tblFuncionarios VALUES (4,'Herline',3,100.00)

    INSERT INTO tblFuncionarios VALUES (5,'Ricardo',NULL,600.00)

    INSERT INTO tblFuncionarios VALUES (6,'Cléber',5,500.00)

    INSERT INTO tblFuncionarios VALUES (7,'Eduardo',5,300.00)

     

    WITH Inferiores AS (

    SELECT

    IDFuncionario, NomeFuncionario,

    IDSuperior, ValorVendido

    FROM tblFuncionarios

    UNION ALL

    SELECT

    SUP.IDFuncionario, SUP.NomeFuncionario,

    SUP.IDSuperior, INF.ValorVendido

    FROM

    Inferiores AS INF

    INNER JOIN tblFuncionarios AS SUP ON INF.IDSuperior = SUP.IDFuncionario),

    I AS (

    SELECT

    IDFuncionario, NomeFuncionario,

    IDSuperior, SUM(ValorVendido) AS TotalVendas

    FROM Inferiores

    GROUP BY IDFuncionario, NomeFuncionario, IDSuperior)

     

    SELECT I.IDFuncionario, I.NomeFuncionario, I.IDSuperior,

    I.TotalVendas, F.ValorVendido, I.TotalVendas - F.ValorVendido as dif

    FROM I

    INNER JOIN tblFuncionarios F ON I.IDFuncionario = F.IDFuncionario

     

     

    [ ]s,

     

    Gustavo

    quarta-feira, 15 de outubro de 2008 14:15

Todas as Respostas

  • Boa Tarde,

     

    Abordei um exemplo muito parecido em um Webcast.

    Maiores detalhes em:

     

    Dicas e Truques sobre consultas complexas no SQL Server

    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!162.entry

     

    [ ]s,

     

    Gustavo

    terça-feira, 14 de outubro de 2008 20:10

  • Gustavo, bom dia

    Muito obrigado, a maneira como você fez mostra apenas o nivel superior com a soma dos niveis inferiores para mostrar todos os niveis na mesma consulta teria que usar algo acumulativo ?

    Abraço
    Charbel
    quarta-feira, 15 de outubro de 2008 13:16
  • Olá Charbel,

     

    Essa deu um trabalhinho... O segredo era inverter a ordem. Ao invés de descer do topo à base, fui da base ao topo.

    Segue o exemplo:

     

    Code Snippet

    CREATE TABLE tblFuncionarios (

    IDFuncionario INT,

    NomeFuncionario VARCHAR(50),

    IDSuperior INT NULL,

    ValorVendido SMALLMONEY)

     

    -- Insere os registros

    INSERT INTO tblFuncionarios VALUES (1,'Thelma',NULL,350.00)

    INSERT INTO tblFuncionarios VALUES (2,'Denise',1,250.00)

    INSERT INTO tblFuncionarios VALUES (3,'Tamara',2,200.00)

    INSERT INTO tblFuncionarios VALUES (4,'Herline',3,100.00)

    INSERT INTO tblFuncionarios VALUES (5,'Ricardo',NULL,600.00)

    INSERT INTO tblFuncionarios VALUES (6,'Cléber',5,500.00)

    INSERT INTO tblFuncionarios VALUES (7,'Eduardo',5,300.00)

     

    WITH Inferiores AS (

    SELECT

    IDFuncionario, NomeFuncionario,

    IDSuperior, ValorVendido

    FROM tblFuncionarios

    UNION ALL

    SELECT

    SUP.IDFuncionario, SUP.NomeFuncionario,

    SUP.IDSuperior, INF.ValorVendido

    FROM

    Inferiores AS INF

    INNER JOIN tblFuncionarios AS SUP ON INF.IDSuperior = SUP.IDFuncionario),

    I AS (

    SELECT

    IDFuncionario, NomeFuncionario,

    IDSuperior, SUM(ValorVendido) AS TotalVendas

    FROM Inferiores

    GROUP BY IDFuncionario, NomeFuncionario, IDSuperior)

     

    SELECT I.IDFuncionario, I.NomeFuncionario, I.IDSuperior,

    I.TotalVendas, F.ValorVendido, I.TotalVendas - F.ValorVendido as dif

    FROM I

    INNER JOIN tblFuncionarios F ON I.IDFuncionario = F.IDFuncionario

     

     

    [ ]s,

     

    Gustavo

    quarta-feira, 15 de outubro de 2008 14:15
  • Gustavo , Obrigado novamente isso é extamente o que precisava valeu !!! , poderia so me tirar algumas duvidas deixei umas perguntas comentadas no codigo. Obrigado !!!

    WITH Inferiores AS (

    --SQL ANCORA

    SELECT

    IDFuncionario, NomeFuncionario,

    IDSuperior, ValorVendido

    FROM tblFuncionarios

    UNION ALL

    -- Parte Recursiva

    SELECT

    SUP.IDFuncionario, SUP.NomeFuncionario,

    SUP.IDSuperior, INF.ValorVendido

    FROM

    Inferiores AS INF

    INNER JOIN tblFuncionarios AS SUP ON INF.IDSuperior = SUP.IDFuncionario),

     

    --Essa parte vc deu um Alias para Inferiores?

    I AS (

    --

    -- Como esse sql trabalha aqui dentro?

    SELECT

    IDFuncionario, NomeFuncionario,

    IDSuperior, SUM(ValorVendido) AS TotalVendas

    FROM Inferiores

    GROUP BY IDFuncionario, NomeFuncionario, IDSuperior

    --

    )

    -- Aqui você relacionou a CTE Inferiores apelidada dentro da estrutura acima de "I" com a tabela tblFuncionarios isso mesmo?

    SELECT I.IDFuncionario, I.NomeFuncionario, I.IDSuperior,

    I.TotalVendas, F.ValorVendido, I.TotalVendas - F.ValorVendido as dif

    FROM I

    INNER JOIN tblFuncionarios F ON I.IDFuncionario = F.IDFuncionario

     

     

    quinta-feira, 16 de outubro de 2008 18:50
  • Olá Charbel,

     

    Toda CTE seja recursiva ou não precisa de uma nome. Como estou subindo da base para o topo, eu a chamei de "Inferiores". A primeira iteração a CTE irá executar a âncora e retornar todos os registros de funcionário. Na segunda iteração, ela irá juntar os registros da primeira iteração e fazer um JOIN para recuperar os superiores da primeira iteração. Thelma e Ricardo não tem superiores, mas Tamara, Herline, Denise, Cleber, etc tem. A CTE irá fazer várias iterações até que a base (aqueles funcionários que não são superiores de ninguém) tenha conseguido chegar até o topo (Herline tem que fazer várias iterações até chegar em Telma).

     

    A cada iteração, o salário é acumulado, ou seja, Herline tem o seu salário na primeira iteração, mas ele vai se acumulando ao salário do seu superior até que Herline chegue no seu topo (no caso a Thelma).

     

    Só que essa CTE só tem os valores acumulados e não os individuais e nem a diferença. Então dei um apelido para a CTE chamado I e fiz a junção dessa CTE com a tabela de funcionários. A CTE retorna o valor acumulado para cada funcionário. Fazendo essa junção com a tabela de funcionários, poderei obter o valor individual de cada funcionário e tendo o valor acumulado da CTE e o valor individual de funcionários posso calcular a diferença.

     

    CTEs são poderosas, mas podem nos colocar em algumas complexidades. Confesso que tive que pensar um pouco na hora de montar esse exemplo.

     

    [ ]s,

     

    Gustavo

    sexta-feira, 17 de outubro de 2008 02:17
  •  Gustavo Maia Aguiar wrote:

    Olá Charbel,

     

    Toda CTE seja recursiva ou não precisa de uma nome. Como estou subindo da base para o topo, eu a chamei de "Inferiores". A primeira iteração a CTE irá executar a âncora e retornar todos os registros de funcionário. Na segunda iteração, ela irá juntar os registros da primeira iteração e fazer um JOIN para recuperar os superiores da primeira iteração. Thelma e Ricardo não tem superiores, mas Tamara, Herline, Denise, Cleber, etc tem. A CTE irá fazer várias iterações até que a base (aqueles funcionários que não são superiores de ninguém) tenha conseguido chegar até o topo (Herline tem que fazer várias iterações até chegar em Telma).

     

    A cada iteração, o salário é acumulado, ou seja, Herline tem o seu salário na primeira iteração, mas ele vai se acumulando ao salário do seu superior até que Herline chegue no seu topo (no caso a Thelma).

     

    Só que essa CTE só tem os valores acumulados e não os individuais e nem a diferença. Então dei um apelido para a CTE chamado I e fiz a junção dessa CTE com a tabela de funcionários. A CTE retorna o valor acumulado para cada funcionário. Fazendo essa junção com a tabela de funcionários, poderei obter o valor individual de cada funcionário e tendo o valor acumulado da CTE e o valor individual de funcionários posso calcular a diferença.

     

    CTEs são poderosas, mas podem nos colocar em algumas complexidades. Confesso que tive que pensar um pouco na hora de montar esse exemplo.

     

    [ ]s,

     

    Gustavo

     

    Opa Gustavo, desculpa mas recursao nao entra na minha cabeça... Se eu quisesse apenas os dados hierarquicamente ( apenas os nomes mesmo ) como ficaria ? Eu tentei aqui mas ficou estranho, minha estrutura eh a seguinte:

     

    codigo PK

    codigopai FK

    nome

    status

     

     

    WITH Inferiores AS (

    SELECT codigo, nome, codigopai, status FROM CATEGORIA

    UNION ALL

    SELECT SUP.codigo, SUP.nome, SUP.codigopai, SUP.status FROM Inferiores AS INF

    INNER JOIN CATEGORIA AS SUP ON INF.codigopai = SUP.codigo),

    I AS (SELECT codigo, codigopai, nome FROM Inferiores)

    SELECT I.codigo, I.nome, I.codigopai FROM I INNER JOIN CATEGORIA ON CATEGORIA.codigo = I.codigo

    quarta-feira, 29 de outubro de 2008 21:41
  • Olá GJunior,

     

    Poste sua dúvida, sua estrutura e o que deseja como retorno em uma nova thread e terei prazer em ajudá-lo (se ninguém o fizer antes).

     

    [ ]s,

     

    Gustavo

     

    quarta-feira, 29 de outubro de 2008 23:26
  • Olá GJunior,

     

    Poste sua dúvida, sua estrutura e o que deseja como retorno em uma nova thread e terei prazer em ajudá-lo (se ninguém o fizer antes).

     

    [ ]s,

     

    Gustavo

     

    Olá Gustavo, sei que o post é meio antigo, mas minha dúvida é meio parecida com a do colega acima. Eu entendo perfeitamente as CTEs, inclusive as recursivas. Porem minha dificuldade na recursão é saber como funciona a iteração do sql server. Pegando o seguinte exemplo do seu site:

    – Cria uma tabela de Plano de Contas
    CREATE TABLE PlanoContas (
        ID INT NOT NULL,
        IDSup INT NULL,
        Descricao VARCHAR(50))

    – Adiciona as constraints
    ALTER TABLE PlanoContas ADD CONSTRAINT PK_Plano PRIMARY KEY (ID)

    ALTER TABLE PlanoContas ADD CONSTRAINT FK_Plano FOREIGN KEY (IDSup)
    REFERENCES PlanoContas (ID)

    – Insere os registros no Plano de Contas
    INSERT INTO PlanoContas VALUES (01,NULL,‘Ativo’)
    INSERT INTO PlanoContas VALUES (02,01,‘Ativo Circulante’)
    INSERT INTO PlanoContas VALUES (03,02,‘Disponibilidades’)
    INSERT INTO PlanoContas VALUES (04,03,‘Caixa’)
    INSERT INTO PlanoContas VALUES (05,04,‘Caixa Geral’)
    INSERT INTO PlanoContas VALUES (06,03,‘Contas Bancárias’)
    INSERT INTO PlanoContas VALUES (07,06,‘Conta Corrente’)
    INSERT INTO PlanoContas VALUES (08,06,‘Conta Poupança’)

    – Mostra os caminhos até as contas
    ;WITH Res (ID, IDSup, Descricao) As (
        SELECT ID, IDSup, CAST(Descricao As VARCHAR(MAX)) FROM PlanoContas
        WHERE IDSup IS NULL
        UNION ALL
        SELECT F.ID, F.IDSup, P.Descricao + ‘ -> ‘ + F.Descricao
        FROM Res As P
        INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

    SELECT ID, Descricao FROM Res
    ORDER BY ID

    Resultado:

    ID Descricao
    01 Ativo
    02 Ativo -> Ativo Circulante
    03 Ativo -> Ativo Circulante -> Disponibilidades
    04 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa
    05 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa -> Caixa Geral
    06 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias
    07 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Corrente
    08 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Poupança

    Bom veja a minha dúvida, o servidor vai executar o script então ele chega na clausula with e ve que se trata de uma CTE. Entra na CTE, ve a definição da CTE e ai parte para a execução do select externo. Ao executar o select externo ele chama a CTE, que executa o primeiro select (o ancora), pega o unico registro e coloca no result set, entao executa o select abaixo do union all, o result set da CTE ja tem um registro cujo ID é igual a 01 entao no join com a tabela Plano conta ele acha apenas o registro Ativo Circulante, colocando o segundo registro no result set da CTE (Ativo -> Ativo Circulante) e o resto do fluxo como fica? Vc poderia me explicar? 


    --
    Gerson C. Júnior
    http://www.ekow.com.br

    terça-feira, 15 de janeiro de 2013 18:17