Lição 1: Construindo consultas recursivas com CTE


 

O conceito de CTE é muito similar a tabelas derivadas, porém com CTEs você pode cruzar com outros resultados para resolver um ou mais problemas, executando consultas recursivas.

 

Expressão de Tabela Comum (Common Table Expressions)

 

Sintaxe:

WITH – Cláusula onde contém o comando válido de select.

Select – Consulta que será realizada utilizando a CTE.

 

WITH EmpTitle AS

(SELECT JobTitle, COUNT(*) NumTitles

FROM HumanResources.Employee

GROUP BY JobTitle)

 

SELECT b.BusinessEntityID, b.JobTitle, a.NumTitles

FROM EmpTitle a INNER JOIN HumanResources.Employee b ON b.JobTitle = a.JobTitle

GO

 

Uma CTE recursiva expande a definição de uma expressão de tabela e consiste em duas partes:

– Uma consulta âncora, que é a fonte da recursividade, juntamente com a declaração “UNION ALL” e uma segunda consulta.

– Uma consulta externa que referencia a rotina e especifica o numero de níveis de recursão.

Por exemplo, a consulta abaixo permite que você especifique um employee e retorna  os niveis da gerencia acima desse empregado na organização, com um limite de 25 niveis de organização.

 

DECLARE @EmployeeToGetOrgFor INT=126;

WITH EMP_cte(BusinessEntityID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel)

AS (SELECT E.BusinessEntityID, E.OrganizationNode, P.FirstName, P.LastName, E.JobTitle, 0

       FROM HumanResources.Employee e INNER JOIN Person.Person AS p

           ON P.BusinessEntityID = E.BusinessEntityID

       WHERE E.BusinessEntityID = @EmployeeToGetOrgFor

  UNION ALL

      SELECT E.BusinessEntityID, E.OrganizationNode, P.FirstName, P.LastName,     E.JobTitle, RecursionLevel+1

    FROM HumanResources.Employee E INNER JOIN EMP_cte

           ON E.OrganizationNode = EMP_cte.OrganizationNode.GetAncestor(1)

       INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityID)

 

SELECT EMP_cte.RecursionLevel, EMP_cte.BusinessEntityID, EMP_cte.FirstName, EMP_cte.LastName,   EMP_cte.OrganizationNode.ToString() AS OrganizationNode,

P.FirstName AS ‘ManagerFirstName’, P.LastName AS ‘ManagerLastName’

FROM EMP_cte INNER JOIN HumanResources.Employee E

                         ON EMP_cte.OrganizationNode.GetAncestor(1) = E.OrganizationNode

                      INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityID

ORDER BY RecursionLevel,EMP_cte.OrganizationNode.ToString()

OPTION (MAXRECURSION 25);

 

image

 

A primeira consulta com a cláusula WITH define o resultado ancora. A segunda consulta executada recursivamente até o nível máximo de recursão contra a consulta âncora.

A recursividade é realizada pelo INNER JOIN na CTE:

INNER JOIN EMP_cte

A consulta externa é então utilizada para retornar o resultado da operação recursiva enquanto dados adicionais são necessários. A cláusula “OPTION” especifica o número máximo de níveis de recursão serão permitidos.

Dica para o exame:

Caso haver um bloco de comando antes da palavra chave “WITH” utilizar “;” para finalizar o bloco de comando anterior.

 

[]’s

Andressa Alves Martins

Twitter: Dre_Martins Skype: Dre.alvesm

Blog: http://sqlgo.wordpress.com

Anúncios

2 Responses to Lição 1: Construindo consultas recursivas com CTE

  1. Andressa disse:

    Pois é, mal postei e já estou aqui enchendo de novo.

    Serie de posts interessantes sobre CTE:

    http://zavaschi.com/index.php/category/common-table-expression/

    =)

  2. vagnerwolf disse:

    Muito obrigado pelo Post, ajudou bastante, não diria que quebrou um galho pois não seria ecológico, mas digamos que removeu uma arvore caída na minha estrada. Hehehe

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: