SQL Server Common Table Expressions (CTE) usage and examples (Português)

By: Jayendra Viswanathan | Updated: 2018-03-27 | Comments (2) | Related: More > Scripts

Problem

muitas organizações têm algum tipo de hierarquia para processos de negócios. Quando se trata de grandes organizações, a hierarquia pode ficar muito complexa e grande, então construir uma hierarquia em um RDBMS é uma tarefa tediosa. Temos de criar visualizações, Cursores e em breve, mas usar um CTE no servidor SQL é uma solução melhor para recuperar dados baseados na hierarquia e nesta dica, vou mostrar-lhe como.,

Solution

Common Table Expressions (CTE) have two types, recursive and non-recursive. Veremos como o CTE recursivo funciona com exemplos nesta dica.

uma ETI recursiva pode ser explicada em três partes:

• pesquisa de âncora: esta é a primeira afirmação que é executada. Este pedido fornecerá os dados de base para o CTE.separador
• : esta é a parte do meio onde geralmente usamos uma união e mais alguns operadores.
• consulta recursiva: esta é a parte principal, esta é a consulta CTE que se refere ao mesmo CTE por recursão.,

vamos criar um exemplo de CTE

vamos ter um cenário de uma organização (org) gráfico. Neste exemplo, a organisation chart partiria de” CEO “e acabaria no”PurchaseDepartment”. Cada departamento / pessoa está ligado ao antecessor asnodes. Vamos ver como um CTE pode ser usado para alcançar isso no servidor SQL. Nós também vamos basear – nos em como usar MAXRECURSION ao usar um CTE.

vamos criar uma tabela de amostras.

Agora a tabela é criada e podemos povoar a tabela com os valores da tabela “MyDepartment”.,As declarações de inserção abaixo podem ser executadas para inserir os dados na tabela “MyDepartment”.

O ParentID para a árvore de topo é definido como NULL, indicando que existe noparent para esta linha. Agora que carregamos os dados, podemos codificar uma consulta para walkthe hierarchy usando uma expressão de tabela comum. precisamos relatar toda a estrutura organizacional sob o CEO. O seguinte código recursivo usando um CTE nos dará a saída:

WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)AS( SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree FROM MyDepartment WHERE ParentID IS NULL UNION ALL SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1 FROM MyDepartment JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID) SELECT * FROM OrgTree ORDER BY Tree

abaixo está a saída da execução da consulta acima.,/td>

Anchor Query

Creating tables is one piece of it, inserting data is another group in the example.,A parte importante é a implementação do CTE usando a cláusula com. Para nosso exemplo, o nome do CTE é nomeado como “OrgTree”. A primeira seleção no CTEis usada para extrair o primeiro nó da árvore que é “CEO” e o ID do pai é definido como nulo. A consulta abaixo irá obter o primeiro nó em nosso exemplo.

SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree FROM MyDepartmentWHERE ParentID IS NULL

Abaixo vou explicar os dados., O lado esquerdo tem os dados dos pais e o lado direito tem os dados das crianças. Se notar que o DepartmentID 1 (lado esquerdo) é o progenitor do DepartmentID (lado direito) 2 e 3. Se você olhar mais além cada departamento está relacionado com o ParentID na mesa de crianças. Abaixo está a representação da imagem da consulta que foi gerada acima. A seta liga o departamento e o ParentId para a nossa referência.

separador e consulta recursiva

a próxima secção é a junção interna que combina o CTE onde, na recursão, surge uma imagem, que o intern se refere a si próprio., A junção interna recupera os dados, colocando a tabela “MyDepartment” em duas partes usando o OrgTreeCTE e faz uma junção e cria o CTE para que possamos consultar o CTE.

SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1FROM MyDepartmentJOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID

Query Designer

a imagem do ecrã abaixo está disponível para vermos a consulta no Designer. O lado direito é o CTE – “OrgTree”, uma vez que o CTE será criado após a execução, o desenhador de consultas não mostra as colunas, se younotice a tabela” MyDepartment ” tem a coluna e a referência de junção interna.,

MAXRECURSION

Quando se trata de usar uma CTE um dos problemas enfrentados é um loop infinito whileforming CTE. Em geral, quando a consulta Pai e filho retorna o mesmo valor orequal, o CTE pode entrar em um loop infinito e a transação pode ir para um loop infinito. Para evitar isto, existe uma cláusula de opção que pode ser usada no fim do comando CTE SELECT com a palavra-chave MAXRECURSION e a contagem de linhas. O Using0 não tem restrições, mas no nosso exemplo eu usei um valor de 10.,

WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)AS( SELECT DepartmentID, DepartmentName, ParentID, 0 AS Tree FROM MyDepartment WHERE ParentID IS NULL UNION ALL SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID, OrgTree.Tree + 1 FROM MyDepartment JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID)SELECT * FROM OrgTree OPTION (MAXRECURSION 10) 

Consultas de Exemplo

Tente estas consultas de exemplo para localizar os dados e veja se você pode vir acima com otherscenarios e como consultar os dados.

-- return everyone under Program Manager (ParentID = 8)WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)AS( SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree FROM MyDepartment WHERE ParentID = 8 UNION ALL SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1 FROM MyDepartment JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID)SELECT * FROM OrgTree;-- return Vice President (DepartmentID = 4) and direct reports (ParentID = 4)WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)AS( SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree FROM MyDepartment WHERE DepartmentID = 4 UNION ALL SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1 FROM MyDepartment JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID WHERE MyDepartment.ParentID = 4)SELECT * FROM OrgTree; -- return everyone above Senior Manager (DepartmentID = 6)WITH OrgTree(DepartmentName,ParentID,ReportsTo)AS( SELECT T1.DepartmentName,T2.DepartmentID,T2.DepartmentName FROM MyDepartment T1 INNER JOIN MyDepartment T2 ON T1.ParentID=T2.DepartmentID WHERE T1.DepartmentID=6 UNION ALL SELECT OT.ReportsTo,T2.DepartmentID,T2.DepartmentName FROM OrgTree OT INNER JOIN MyDepartment T1 ON OT.ParentID=T1.DepartmentID INNER JOIN MyDepartment T2 ON T1.ParentID=T2.DepartmentID)SELECT * FROM OrgTree;-- return list with of people with no direct reportsWITH OrgTree(ParentID, DepartmentID, DepartmentName, DepartmentLevel) AS ( SELECT ParentID, DepartmentID, DepartmentName, 0 AS DepartmentLevell FROM MyDepartment WHERE ParentID IS NULL UNION ALL SELECT e.ParentID, e.DepartmentID, e.DepartmentName, DepartmentLevel + 1 FROM MyDepartment AS e INNER JOIN OrgTree AS d ON e.ParentID = d.DepartmentID )SELECT * FROM OrgTree WHERE DepartmentLevel = 5;

próximas etapas

• CTEs são uma característica poderosa no servidor SQL. A sintaxe é fácil de usar e podemos construir qualquer tipo de tabelas hierárquicas e dados baseados em nossas necessidades usinga CTE.,
• dê uma olhada em algumas dessas outras dicas:
  • Consultas Recursivas usando Expressões de Tabela Comuns (CTE) no SQL Server
  • Como usar o SQL Server CTEs para tornar o seu código T-SQL legível por humanos
  • SQL Server CTE e XML Recursão Script

Última actualização: 2018-03-27

Sobre o autor

Jayendra é um Líder de Projeto, com muitos anos de experiência em TI., Ele tem um forte conhecimento em desenvolvimento de software e gerenciamento de projetos.
View all my tips

Related Resources

• More Database Developer Tips…