Compartilhar via


Usando expressões de tabela comuns

Podemos pensar em uma expressão de tabela comum (CTE) como sendo um conjunto de resultados temporário definido no escopo de execução de uma única instrução SELECT, INSERT, UPDATE, DELETE ou CREATE VIEW. Uma CTE é semelhante a uma tabela derivada que não é armazenada como um objeto e permanece apenas durante a duração de uma consulta. Ao contrário de uma tabela derivada, uma CTE pode ser auto-referenciada e pode ser referenciada várias vezes na mesma consulta.

Uma CTE pode ser usada para:

  • Criar uma consulta recursiva. Para obter mais informações, consulte Consultas recursivas que usam expressões de tabelas comuns.

  • Substituir uma exibição quando o uso geral da exibição não for necessário, ou seja, você não precisa armazenar a definição nos metadados.

  • Habilitar o agrupamento por uma coluna que seja derivada de uma subseleção escalar ou uma função que não seja determinística ou tenha acesso externo.

  • Referenciar a tabela resultante várias vezes na mesma instrução.

Usar uma CTE oferece as vantagens de legibilidade aprimorada e facilidade de manutenção de consultas complexas. A consulta pode ser dividida em blocos de construção lógicos simples e separados. Esses blocos simples podem ser usados para criar CTEs interinas mais complexas, até que o resultado final seja gerado.

As CTEs podem ser definidas em rotinas definidas pelo usuário, como funções, procedimentos armazenados, gatilhos ou exibições.

Estrutura de uma CTE

Uma CTE é constituída de um nome de expressão representando a CTE, uma lista de colunas opcional e uma consulta que define a CTE. Após a definição da CTE, ela poderá ser referenciada como uma tabela ou exibição em uma instrução SELECT, INSERT, UPDATE ou DELETE. Uma CTE também pode ser usada em uma instrução CREATE VIEW como parte da instrução SELECT que a define.

A estrutura de sintaxe básica de uma CTE é:

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

A lista de nomes de colunas será opcional somente se forem fornecidos nomes distintos para todas as colunas resultantes na definição da consulta.

A instrução para executar a CTE é:

SELECT <column_list>

FROM expression_name;

Exemplo

O exemplo a seguir mostra os componentes da estrutura da CTE: nome da expressão, lista de colunas e consulta. A expressão da CTE Sales_CTE tem três colunas (SalesPersonID, NumberOfOrders e MaxDate) e está definida como o número total de ordens de venda e data da ordem de venda mais recente na tabela SalesOrderHeader para cada vendedor. Quando a instrução é executada, a CTE é referenciada duas vezes: uma vez para retornar as colunas selecionadas para o vendedor e novamente para recuperar detalhes semelhantes para o gerente do vendedor. Os dados para o vendedor e o gerente são retornados em uma única linha.

USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN Sales_CTE AS OS
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN Sales_CTE AS OM
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO

Eis um conjunto parcial de resultados:

EmployeeID  NumberOfOrders MaxDate  ManagerID NumberOfOrders MaxDate
----------- -------------- ---------- --------- -------------- ----------
268         48             2004-06-01 273       NULL           NULL
275         450            2004-06-01 268       48             2004-06-01
276         418            2004-06-01 268       48             2004-06-01
277         473            2004-06-01 268       48             2004-06-01

Consulte também

Referência