none
Query com pai e filho RRS feed

  • Pergunta

  •  

    Olá,

     

    Tenho uma tabela de plano de produtos com relacionamento de pai e filho da seguinte maneira:

    Codigo | Descricao | Codigo Pai

    001 | Acessorios |Null

    001.001 | Acessoriois 1 | 001

    001.001.001 Acessorios 2 |001.001

     

    E tenho uma tabela de estoque com o código de último nivel do plano do produto (001.001.001)

     

    Queria fazer uma query que me mostrasse o total em estoque para cada nivel assim?

     

    001 | 100 Unidades

    001.001 |100 Unidades

    001.001.001 | 50 Unidades

    001.001.002 | 50 Unidades

     

    Mas não consegui fazer o select de jeito nenhum.

     

    Alguém poderia me ajudar?

     

    Grato

    sexta-feira, 12 de dezembro de 2008 17:49

Respostas

  • Boa Tarde,

     

    Normalmente eu recorreria a CTE, mas o código é mais trabalhoso. Um código mais simples seria o postado abaixo:

     

    Code Snippet

    DECLARE @M TABLE (Codigo VARCHAR(20), Descricao VARCHAR(50), CodigoPai VARCHAR(20))

    INSERT INTO @M VALUES ('001','Acessorios',Null)

    INSERT INTO @M VALUES ('001.001','Acessorios 1','001')

    INSERT INTO @M VALUES ('001.001.001','Acessorios 1.1','001.001')

    INSERT INTO @M VALUES ('001.001.002','Acessorios 1.2','001.001')

     

    DECLARE @E TABLE (Codigo VARCHAR(20), Estoque INT)

    INSERT INTO @E VALUES ('001.001.001',50)

    INSERT INTO @E VALUES ('001.001.002',50)

     

    -- Recupera os estoques

    SELECT M.Codigo, M.Descricao, SUM(Estoque) AS TotalEstoque FROM @M AS M

    LEFT OUTER JOIN @E AS E ON M.Codigo = SUBSTRING(E.Codigo,1,LEN(M.Codigo))

    GROUP BY M.Codigo, M.Descricao

     

    Se a performance pesar, avise-me para postar a alternativa baseada em CTE (2005 e superiores somente).

     

    [ ]s,

     

    Gustavo

    sexta-feira, 12 de dezembro de 2008 18:00
  • Miguel,

     

    Uma CTE (expressão de tabela comum) fornece a significativa vantagem de ser capaz de se auto-referenciar, criando, portanto uma CTE recursiva. Uma CTE recursiva é aquela em que uma CTE inicial é executada repetidamente para retornar subconjuntos de dados até que o resultado completo seja obtido.

     

    Uma consulta é denominada consulta recursiva quando faz referência a uma CTE recursiva. Retornar dados hierárquicos é de uso comum em consultas recursivas. Por exemplo: exibir funcionários em um organograma ou dados em um cenário de lista de materiais em que um produto pai tenha um ou mais componentes, podendo esses componentes, por outro lado, ter subcomponentes ou componentes de outros pais.

     

    Uma CTE recursiva pode simplificar muitíssimo um código necessário à execução de uma consulta recursiva dentro de instruções SELECT, INSERT, UPDATE, DELETE ou CREATE VIEW. Em versões anteriores do SQL Server, uma consulta recursiva, em geral, requer o uso de tabelas temporárias, cursores e lógica para controlar o fluxo das etapas recursivas.

     

    Create Table Tabela

    (Id Int,

    IdProd Int,

    Custo Float,

    DataCompra DateTime)

    Insert Into Tabela Values(1,5,66.60, GETDATE())

    Insert Into Tabela Values(2,5,08.61, GETDATE()-2)

    Insert Into Tabela Values(3,5,14.62, GETDATE()-1)

    Insert Into Tabela Values(4,8,28.63, GETDATE()+1)

    Insert Into Tabela Values(5,8,32.64, GETDATE()+2)

    Insert Into Tabela Values(6,8,64.65, GETDATE()+3)

     

    Select * from Tabela;

    With SomaData(IdProd, Data)

    As (

    Select IdProd, MAX(DataCompra) from Tabela

    Group By IdProd

    )

    Select T.Id, S.IdProd, T.Custo, S.Data

    from Tabela T Inner Join SomaData S

    On T.DataCompra = S.Data

     

     

    --Sem CTE --

    Select T.Id, S.IdProd, T.Custo, S.Data

    from Tabela T Inner Join (Select IdProd, MAX(DataCompra) As Data from Tabela Group By IdProd) S

    On T.DataCompra = S.Data

     

     

     

     

    sexta-feira, 12 de dezembro de 2008 18:30

Todas as Respostas

  • Boa Tarde,

     

    Normalmente eu recorreria a CTE, mas o código é mais trabalhoso. Um código mais simples seria o postado abaixo:

     

    Code Snippet

    DECLARE @M TABLE (Codigo VARCHAR(20), Descricao VARCHAR(50), CodigoPai VARCHAR(20))

    INSERT INTO @M VALUES ('001','Acessorios',Null)

    INSERT INTO @M VALUES ('001.001','Acessorios 1','001')

    INSERT INTO @M VALUES ('001.001.001','Acessorios 1.1','001.001')

    INSERT INTO @M VALUES ('001.001.002','Acessorios 1.2','001.001')

     

    DECLARE @E TABLE (Codigo VARCHAR(20), Estoque INT)

    INSERT INTO @E VALUES ('001.001.001',50)

    INSERT INTO @E VALUES ('001.001.002',50)

     

    -- Recupera os estoques

    SELECT M.Codigo, M.Descricao, SUM(Estoque) AS TotalEstoque FROM @M AS M

    LEFT OUTER JOIN @E AS E ON M.Codigo = SUBSTRING(E.Codigo,1,LEN(M.Codigo))

    GROUP BY M.Codigo, M.Descricao

     

    Se a performance pesar, avise-me para postar a alternativa baseada em CTE (2005 e superiores somente).

     

    [ ]s,

     

    Gustavo

    sexta-feira, 12 de dezembro de 2008 18:00
  • 2 Processadores Core 2 Quad

    4 Gb RAM

     

    W2K8 + SQL 2K8

     

    1151 Registros em Plano de Produto

    3.000.000 registros em saldo de estoque

    200.000 registros em um dia (sempre filtro um dia específico, normalmente max(dat_saldo) ou seja a ultima posição)

     

    38 segundos para rodar a query (sem indexação apropriada ainda)

     

    a query ficou assim:

     

    Code Snippet

    SELECT M.COD_PLANO_PRODUTO, M.DES_PLANO_PRODUTO, SUM(QTD_ESTOQUE) AS TotalEstoque

    FROM DIM_PLANO_PRODUTO AS M

    LEFT OUTER JOIN FAT_ESTOQUE AS E

    ON M.COD_PLANO_PRODUTO = SUBSTRING(E.COD_PLANO_PRODUTO,1,LEN(M.COD_PLANO_PRODUTO))

    where E.DAT_SALDO = '2008-12-11'

    GROUP BY M.COD_PLANO_PRODUTO, M.DES_PLANO_PRODUTO

    order by 1,2

     

     

    Como funciona a outra opção? com CTE (o que é CTE?)

     

    Muito Obrigado Gustavo!

    sexta-feira, 12 de dezembro de 2008 18:17
  • Miguel,

     

    Uma CTE (expressão de tabela comum) fornece a significativa vantagem de ser capaz de se auto-referenciar, criando, portanto uma CTE recursiva. Uma CTE recursiva é aquela em que uma CTE inicial é executada repetidamente para retornar subconjuntos de dados até que o resultado completo seja obtido.

     

    Uma consulta é denominada consulta recursiva quando faz referência a uma CTE recursiva. Retornar dados hierárquicos é de uso comum em consultas recursivas. Por exemplo: exibir funcionários em um organograma ou dados em um cenário de lista de materiais em que um produto pai tenha um ou mais componentes, podendo esses componentes, por outro lado, ter subcomponentes ou componentes de outros pais.

     

    Uma CTE recursiva pode simplificar muitíssimo um código necessário à execução de uma consulta recursiva dentro de instruções SELECT, INSERT, UPDATE, DELETE ou CREATE VIEW. Em versões anteriores do SQL Server, uma consulta recursiva, em geral, requer o uso de tabelas temporárias, cursores e lógica para controlar o fluxo das etapas recursivas.

     

    Create Table Tabela

    (Id Int,

    IdProd Int,

    Custo Float,

    DataCompra DateTime)

    Insert Into Tabela Values(1,5,66.60, GETDATE())

    Insert Into Tabela Values(2,5,08.61, GETDATE()-2)

    Insert Into Tabela Values(3,5,14.62, GETDATE()-1)

    Insert Into Tabela Values(4,8,28.63, GETDATE()+1)

    Insert Into Tabela Values(5,8,32.64, GETDATE()+2)

    Insert Into Tabela Values(6,8,64.65, GETDATE()+3)

     

    Select * from Tabela;

    With SomaData(IdProd, Data)

    As (

    Select IdProd, MAX(DataCompra) from Tabela

    Group By IdProd

    )

    Select T.Id, S.IdProd, T.Custo, S.Data

    from Tabela T Inner Join SomaData S

    On T.DataCompra = S.Data

     

     

    --Sem CTE --

    Select T.Id, S.IdProd, T.Custo, S.Data

    from Tabela T Inner Join (Select IdProd, MAX(DataCompra) As Data from Tabela Group By IdProd) S

    On T.DataCompra = S.Data

     

     

     

     

    sexta-feira, 12 de dezembro de 2008 18:30
  • Junior, 

     

    agora já sei o que é CTE.

    Já entendi onde se aplica a recursividade no meu caso.

     

    Estou agora me divertindo com os novos métodos de acesso aos dados.

     

    Com relação a performance da query anterior, convenci o usuário que melhor que ele rodar uma query e copiar colar no excel seria a criação de um relatório no SSRS, ai tudo ficou mais fácil pra mim, estou usando os recursos de totalização da ferramenta. Inclusive aposto que vai ficar mais simples pra ele também.

     

    Muito Obrigado

    sexta-feira, 12 de dezembro de 2008 18:48
  • Olá Miguel,

     

    A explicação do Jr. está correta. Para ficar mesmo completa, faltou ele dar um exemplo de CTE recursiva e não de uma CTE comum. A CTE recursiva será uma saída para o seu caso, já que se a quantidade de registros é tão grande, o desempenho com a minha solução não será satisfatório. Tente o seguinte:

     

    Code Snippet

    DECLARE @M TABLE (Codigo VARCHAR(20), Descricao VARCHAR(50), CodigoPai VARCHAR(20))

    INSERT INTO @M VALUES ('001','Acessorios',Null)

    INSERT INTO @M VALUES ('001.001','Acessorios 1','001')

    INSERT INTO @M VALUES ('001.001.001','Acessorios 1.1','001.001')

    INSERT INTO @M VALUES ('001.001.002','Acessorios 1.2','001.001')

     

    DECLARE @E TABLE (Codigo VARCHAR(20), Estoque INT)

    INSERT INTO @E VALUES ('001.001.001',50)

    INSERT INTO @E VALUES ('001.001.002',50)

     

    -- Recupera os estoques

    ;WITH Estoques AS (

    SELECT Codigo, Descricao, ISNULL(CodigoPai,Codigo) AS CodigoPai FROM @M

    UNION ALL

    SELECT E.Codigo, E.Descricao, M.CodigoPai FROM @M AS M

    INNER JOIN Estoques AS E ON M.Codigo = E.CodigoPai)

     

    SELECT ISNULL(Estoques.CodigoPai,Estoques.Codigo), SUM(Estoque) FROM Estoques

    LEFT OUTER JOIN @E AS E ON Estoques.Codigo = E.Codigo

    GROUP BY ISNULL(Estoques.CodigoPai,Estoques.Codigo)

     

    Para saber mais sobre CTEs, consulte o link abaixo:

     

    SQL Server 2005 New Features: Common Table Expressions (CTEs)

    http://www.plugmasters.com.br/sys/materias/549/1/SQL-Server-2005-New-Features%3A-Common-Table-Expressions-%28CTEs%29

     

    Para saber mais sobre recursividade, consulte um Webcast de minha autoria na qual descrevo o uso de CTEs.

     

    Webcast - Dicas e truques sobre consultas complexas no SQL Server

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

     

    No SQL Server 2008 existe um tipo Hierachical ID que talvez seja mais eficiente que a CTE. Ainda não aprendi a utilizá-lo para o seu caso.

     

    [ ]s,

     

    Gustavo

    sexta-feira, 12 de dezembro de 2008 19:20
  • Maia,

     

    É verdade eu esqueci.

    sexta-feira, 12 de dezembro de 2008 19:30