Redgate Hub (Português)

introduzido no SQL Server 2005, a expressão comum da tabela (CTE) é um conjunto de resultados com nome temporário que poderá referenciar dentro de uma instrução de selecção, inserção, actualização ou remoção. Você também pode usar um CTE em uma declaração CREATE VIEW, como parte da consulta seleta da view. Além disso, a partir do SQL Server 2008, você pode adicionar um CTE à nova instrução MERGE.

SQL Server supports two types of CTEs-recursive and nonrecursive. Neste artigo, eu explico como criar ambos os tipos., Os exemplos que eu fornecer são baseados em uma instância local do SQL Server 2008 e recuperar dados do AdventureWorks2008 sample database.

trabalhando com expressões de tabela comuns

define Eti adicionando uma cláusula com directamente antes da sua declaração de selecção, inserção, actualização, remoção ou junção.,”>

1 2 3 4 5

] <common_table_expression>::= cte_name )] AS (cte_query)

…which can be represented like this…

As you can see, if you include more than one CTE in your WITH clause, you must separate them with commas., Além disso, para cada CTE, você deve fornecer um nome, a palavra-chave, e uma instrução selecionada. Você também pode fornecer nomes de colunas (separadas por vírgulas), desde que o número de nomes corresponda ao número de colunas retornadas pelo conjunto de resultados.

a instrução SELECT na sua consulta CTE deve seguir os mesmos requisitos que os usados para criar uma vista. Para mais detalhes sobre esses requisitos, consulte o tópico ” CREATE VIEW (Transact-SQL)” nos livros do servidor SQL Online. Para mais detalhes sobre ETI em geral, consulte o tópico “WITH common_table_ expression (Transact-SQL).,”

Depois de definir a sua cláusula com os Eti necessários, pode então referenciar esses Eti como faria com qualquer outra tabela. No entanto, você pode referenciar um CTE apenas dentro do escopo de execução da declaração que segue imediatamente a cláusula com. Depois de executar a sua declaração, O conjunto de resultados CTE não está disponível para outras declarações.

criando uma expressão de tabela comum não-recursiva

um CTE não-recursivo é aquele que não se referencia dentro do CTE. Os ETI não-recursivos tendem a ser mais simples do que os Eti recursivos, e é por isso que estou começando com este tipo.,cteTotalSales:

Depois de especificar que o CTE nome, eu fornecem dois nomes de coluna, SalesPersonID e NetSales, que são colocados entre parênteses e separados por vírgula. Isso significa que o conjunto de resultados retornados pela consulta CTE deve retornar duas colunas.

em seguida, eu forneço a palavra-chave como, em seguida, um conjunto de parênteses que encerram a consulta CTE. Neste caso, a instrução SELECT devolve o total de vendas para cada pessoa de vendas (total de vendas agrupadas por ID de vendedor)., Como você pode ver, a consulta CTE pode incluir funções Transact-SQL, agrupar por cláusulas, ou quaisquer elementos que a instrução seleta em uma definição de view pode incluir.

I can now reference cteTotalSales in the statement that immediately follows. Para este exemplo, eu crio uma instrução seleta que junta as vendas.vSalesPerson vista para cteTotalSales, com base no ID do vendedor. Então eu puxo os nomes e locais da vista e as vendas líquidas do CTE. A tabela seguinte mostra os resultados retornados por esta declaração.,

Como viu anteriormente na sintaxe, pode incluir vários Eti numa cláusula com.,

a primeira CTE-ctetotalales-é semelhante à do exemplo anterior, excepto que a cláusula em que a cláusula foi novamente qualificada para incluir as vendas apenas a partir de 2003. Depois de definir ctetotalales, adiciono uma vírgula, E depois defino cteTargetDiff, que calcula a diferença entre o total de vendas e a quota de vendas.

a nova definição CTE especifica três colunas para o conjunto de resultados: SalesPersonID, SalesQuota e QuotaDiff., Como seria de esperar, a consulta CTE retorna três colunas. A primeira é a identificação do vendedor. A segunda é a quota de vendas. No entanto, como uma quota de vendas não é definida para alguns vendedores, eu uso uma declaração de caso. Se o valor for nulo, esse valor é definido como 0, caso contrário é utilizado o valor real do SalesQuota. a coluna final devolvida é a diferença entre as vendas líquidas e as quotas de vendas. Mais uma vez, uso uma declaração de caso. Se o valor do SalesQuota for nulo, utiliza-se o valor líquido das vendas, CaSO contrário a quota de vendas é subtraída das vendas líquidas para se chegar à diferença.,

algo interessante para notar sobre a segunda consulta CTE é que eu me juntei às vendas.Tabela de vendedores para o primeiro CTE-cteTotalSales – para que eu pudesse calcular a diferença entre as vendas totais e a quota de vendas. Sempre que você definir vários ETI em uma única cláusula, você pode referenciar antes do Eti (mas não o contrário).

Uma vez que eu tenha definido meus CTEs, eu posso referenciá-los na primeira afirmação que segue o CTE, como você viu no exemplo anterior. Neste caso, junto-me às vendas.,vSalesPerson vista para cteTotalSales e, em seguida, juntar-se a cteTargetDiff, tudo baseado no ID do vendedor. Minha lista selecionada então inclui colunas de todas as três fontes. A instrução retorna os resultados mostrados na tabela seguinte.

omo pode ver, os dados de vendas são fornecidos para todos os vendedores, incluindo a cidade em que residem, as suas vendas líquidas, a sua quota de vendas, e a diferença calculada entre os dois números. Neste caso, todas as pessoas excedem bem a quota, quando foi definida uma quota.,

criando uma expressão de tabela comum recursiva

um CTE recursivo é aquele que se referencia dentro desse CTE. O CTE recursivo é útil quando se trabalha com dados hierárquicos porque o CTE continua a ser executado até que a consulta retorna toda a hierarquia.um exemplo típico de dados hierárquicos é uma tabela que inclui uma lista de empregados. Para cada empregado, a tabela fornece uma referência ao gerente dessa pessoa. Essa referência é um ID de empregado dentro da mesma tabela., Você pode usar um CTE recursivo para exibir a hierarquia de dados dos funcionários, como ele apareceria dentro do gráfico organizacional.

Note que um CTE criado incorretamente pode inserir um laço infinito. Para evitar isso, você pode incluir a dica de MAXRECURSION na cláusula de opção da instrução primária Selecionar, Inserir, atualizar, excluir ou juntar. Para obter informações sobre o uso de dicas de consulta, veja o tópico “dicas de consulta (Transact-SQL)” nos livros do servidor de SQL Online.,

para demonstrar como funciona o CTE recursivo, usei as seguintes declarações Transact-SQL para criar e povoar a tabela de empregados na Base de dados AdventureWorks2008:

Como você pode perceber, a base de dados AdventureWorks2008 já inclui os recursos humanos.Mesa de empregados. No entanto, essa tabela agora usa o tipo de dados hierarchyid para armazenar dados hierárquicos, o que introduziria complexidade desnecessária ao tentar demonstrar um CTE recursivo. Por essa razão, criei a minha própria mesa., No entanto, se você quiser experimentar um CTE recursivo sem criar e popularizar uma nova tabela, você pode usar o banco de dados de amostras do AdventureWorks que foi enviado com o servidor sql 2005. Os Recursos Humanos.A tabela do empregado naquele banco de dados armazena os dados de uma maneira similar à tabela i criar acima.,div>2

Como você pode ver, o CTE retorna cinco colunas: EmpID, FirstName, LastName, MgrID, e EmpLevel. A coluna EmpLevel refere-se ao nível na hierarquia em que os empregados se encaixam. O nível mais alto da hierarquia é 1, o nível seguinte é 2, seguido de 3, e assim por diante.

a consulta CTE é por si só composta de duas declarações selecionadas, conectadas com a união de todos os operadores., Uma consulta Cte recursiva deve conter pelo menos dois membros (declarações), conectados pela União todos, União, INTERSETAR, ou exceto o operador. Neste exemplo, a primeira instrução selecionada é o membro âncora, e a segunda instrução é o membro recursivo. Todos os membros âncora devem preceder os membros recursivos, e apenas os membros recursivos podem referenciar o próprio CTE. Além disso, todos os membros devem retornar o mesmo número de colunas com os tipos de dados correspondentes.

agora vamos olhar mais de perto para as próprias declarações., A primeira declaração, o membro âncora, recupera o ID do funcionário, nome próprio, sobrenome e ID do Gerente da tabela de funcionários, onde o ID do gerente é nulo. Este seria o empregado no topo da hierarquia, o que significa que esta pessoa não reporta a ninguém. Consequentemente, o valor de ID do gerente é nulo. Para refletir que esta pessoa está no topo da hierarquia, atribuo um valor de 1 à coluna EmpLevel.

a segunda afirmação na consulta CTE – o membro recursivo-também recupera o ID do empregado, primeiro nome, sobrenome e ID do gerente para os funcionários na tabela de Funcionários., No entanto, observe que eu me junto à mesa de funcionários do próprio CTE. Além disso, o ingresso é baseado no ID de gerente na mesa de funcionários e no ID de funcionário no CTE. Ao fazer isso, o CTE vai fazer loop através da tabela de funcionários até que ele retorna toda a hierarquia.

um outro item a notar sobre a segunda declaração é que, para a coluna EmpLevel, eu adiciono o valor 1 ao valor EmpLevel como aparece no CTE. Dessa forma, cada vez que a declaração faz loops através da hierarquia, o próximo nível correto é aplicado aos funcionários no nível.,

Depois de definir a minha cláusula com, eu crio uma declaração selecionada que recupera os dados do CTE. Note, entretanto, que para a coluna do gerente, eu recupero o primeiro e último nome do empregado associado com o ID do gerente no CTE. Isto permite-me mostrar o nome completo do gerente para cada empregado. A tabela seguinte mostra o conjunto de resultados retornados pela instrução SELECT e seu CTE.,

Como pode ver, o CTE, seja recursivo ou não, pode ser uma ferramenta útil quando necessita de gerar conjuntos de resultados temporários que possam ser acedidos numa instrução de selecção, inserção, actualização, remoção ou junção. Em certo sentido, um CTE é como uma tabela derivada: não é armazenado como um objeto e é válido apenas durante a execução da declaração primária. No entanto, ao contrário da tabela derivada, um CTE pode ser referenciado várias vezes dentro de uma consulta e pode ser auto-referenciação. E o melhor de tudo, os ETI são relativamente fáceis de implementar.,

Você terá notado que Bob está usando AdventureWorks2008 ao invés de AdventureWorks. Se preferir comparar estes exemplos com a base de dados AdventureWorks em vez da Base de dados AdventureWorks2008, deve mudar a coluna de propriedade empresarial para a coluna de vendas PersonID.