As Expressões de Tabela Comuns ou CTE é para breve são utilizados dentro do SQL Server para simplificar o complexo de associações e subconsultas, e para fornecer um meio para consulta de dados hierárquica, tais como um gráfico organizacional. Neste artigo, vamos apresentá-lo a expressões de tabela comuns, os dois tipos de ETI, e seus usos. Além disso, vamos apresentar o CTE geral., Uma vez que você conhece, eu recomendo que você leia estes artigos também:
- Não CTE recursiva do
- CTE Recursiva do
Introdução Comuns Expressões de Tabela
UMA CTE (Common Table Expression) é um resultado temporário definir o que você pode fazer referência dentro de outro SELECT, INSERT, UPDATE, ou DELETE instrução. Eles foram introduzidos na versão do servidor SQL 2005. Eles são compatíveis com SQL e fazem parte da especificação ANSI SQL 99.
um CTE devolve sempre um conjunto de resultados., Eles são usados para simplificar consultas, por exemplo, você poderia usar um para eliminar uma tabela derivada do corpo principal da consulta.
Nota: Todos os exemplos para esta lição são baseados no Microsoft SQL Server Management Studio e na Base de dados AdventureWorks2012. Você pode começar a usar estas ferramentas gratuitas usando o meu guia começar a usar o servidor SQL.
o que é um CTE ou uma expressão de tabela comum no servidor SQL?
a CTE (Common Table Expression) define um conjunto temporário de resultados que poderá então usar numa instrução de selecção. Torna-se uma maneira conveniente de gerenciar consultas complicadas.,
expressões de tabela comuns são definidas dentro da declaração usando o operador com. Você pode definir uma ou mais expressões comuns da tabela desta forma.
aqui está um exemplo realmente simples de um CTE:
WITH Employee_CTE (EmployeeNumber, Title)AS(SELECT NationalIDNumber, JobTitle FROM HumanResources.Employee)SELECT EmployeeNumber, TitleFROM Employee_CTE
vamos quebrar isto um pouco.
A parte azul é o CTE. Notice it contains a query that can be run on its own in SQL., Este é o chamado CTE consulta de definição:
SELECT NationalIDNumber, JobTitleFROM HumanResources.Employee
Quando você executá-lo você verá resultados como:
Repare que, quando definimos o CTE nós dão o resultado de um nome bem como suas colunas. Desta forma, um CTE age como uma vista. O resultado e colunas são nomeados de forma diferente. Isto permite-lhe encapsular a lógica de consulta complicada com a expressão da tabela comum.
agora voltando para o CTE, observe que a declaração com., Aí você verá que o nome e as colunas estão definidas. Estas colunas correspondem às colunas retornadas da consulta interna.
finally notice that our final query references the CTE and columns defined.
da perspectiva da nossa consulta externa, tudo o que ela” vê ” é esta definição. Não se preocupa como o CTE é construído, apenas o seu nome e Colunas.,
Como tal, os resultados da CTE são:
Observe que os nomes de coluna, eles são baseados em aqueles definidos na CTE.
I want to point out that you can definite more than one CTE within a with statement. Isto pode ajudá-lo a simplificar algumas questões muito complicadas que são, em última análise, Unidas. Cada peça complicada pode incluir em seu próprio CTE que é então referido e unido fora da cláusula com.,
aqui está um exemplo usando dois CTE, é um exemplo simples, mas ele mostra como dois CTE são definidos, e então usado em uma junção interna
WITH PersonCTE (BusinessEntityID, FirstName, LastName)AS (SELECT Person.BusinessEntityID, FirstName, LastName FROM Person.Person WHERE LastName LIKE 'C%'),PhoneCTE (BusinessEntityID, PhoneNumber)AS (SELECT BusinessEntityID, PhoneNumber FROM Person.PersonPhone)SELECT FirstName, LastName, PhoneNumberFROM PersonCTEINNER JOINPhoneCTEON PersonCTE.BusinessEntityID = PhoneCTE.BusinessEntityID;
a primeira expressão comum da tabela é de cor verde, o segundo azul. Como você pode ver a partir da instrução selecione os CTE são unidos como se fossem tabelas. Espero que você possa ver que à medida que suas consultas se tornam mais complicadas, CTE pode se tornar uma maneira realmente útil para separar as operações; portanto, simplificar sua consulta final.
por que você precisa de CTE?,
Existem várias razões pelas quais você pode querer usar um CTE sobre outros métodos. Alguns deles incluem:
- legibilidade-a legibilidade promovida pela CTE. Em vez de colocar toda a lógica de consulta em uma consulta Grande, criar vários CTE, que são combinados mais tarde na declaração. Isto permite-lhe obter os pedaços de dados de que necessita e combiná-los numa selecção final.
- substitua uma vista – você pode substituir um CTE por uma vista. Isto é útil se não tiver permissões para criar um objecto de visualização ou se não quiser criar um, uma vez que só é usado nesta consulta.,
- Recursion-Use CTE’s do create recursive queries, that is queries that can call themselves. Isto é útil quando você precisa trabalhar em dados hierárquicos, tais como gráficos de organização.
- limitações-superar as limitações da declaração selecionada, como referenciação em si (recursão), ou realizar o grupo usando funções não-determinísticas.Ranking
i – – sempre que você quiser usar a função ranking como ROW_NUMBER(), RANK(), NTILE() etc.
tipos de CTE
expressões de tabela comuns podem ser colocadas em duas grandes categorias: CTE recursivo e CTE não recursivo.,
CTE recursivo são expressões de tabela comuns que se referenciam. A recursão pode ser um tópico muito difícil de entender, eu realmente não consegui até que eu fiz uma aula de LISP em 1986, mas espero que eu possa explicar para você.
vamos entrar profundamente na recursão numa publicação separada, mas por agora deixe-me apresentar-lhe a recursão usando este diagrama:
Aqui você vê imagem de espelhos opostos. Devido ao reflexo, torna-se uma imagem em uma imagem.as consultas recursivas são assim.,
Como uma consulta recursiva é executada, ela corre repetidamente em um subconjunto dos dados. Uma consulta recursiva é basicamente uma consulta que se chama a si mesma. Em algum momento há uma condição final, então ela não se chama indefinidamente.
de certa forma, quando você olha para a imagem você pode imaginar cada imagem em uma imagem é a imagem chamando a si mesma. No entanto, ao contrário da “reflexão infinita” nos espelhos, chega um ponto em que uma consulta recursiva Encontra a condição final e pára de se chamar.,
nesse ponto, a recursão começa a relaxar, coletar e calcular os dados à medida que analisa cada resultado sucessivo.
CTE não recursivo, como o nome indica, não são recursivos. Eles não se referem a eles próprios. Eles são mais fáceis de entender por isso vamos olhar para eles primeiro em detalhes no próximo artigo desta série.
conclusão
esperançosamente você agora tem uma apreciação do que são CTE e por que nós podemos querer usá-los. Nos próximos dois artigos vamos entrar em detalhes muito maiores sobre o CTE, e quando usá-los.,
até lá, eu recomendaria rever os meus artigos sobre junções e sub-consultas, já que vamos usar estes tipos de consultas para os nossos exemplos.