Nota
O acesso a esta página requer autorização. Pode tentar iniciar sessão ou alterar os diretórios.
O acesso a esta página requer autorização. Pode tentar alterar os diretórios.
Aplica-se a:Ponto de extremidade de análise SQL e Armazém no Microsoft Fabric
As CTEs (Common Table Expressions) podem simplificar consultas complexas desconstruindo consultas normalmente complexas em blocos reutilizáveis.
Existem quatro tipos de CTE, incluindo padrão, sequencial, recursiva e aninhado CTE.
- Uma CTE padrão não faz referência ou define outra CTE em sua definição.
- A definição de uma CTE aninhada inclui a definição de outra CTE.
- A definição de uma CTE sequencial pode fazer referência a uma CTE existente, mas não pode definir outra CTE.
- Um CTE recursivo refere-se a si mesmo em sua definição.
No Microsoft Fabric, o Fabric Data Warehouse e o ponto de extremidade de análise SQL oferecem suporte a CTEs padrão, sequenciais e aninhados , mas não a CTEs recursivos.
Para obter mais informações sobre expressões de tabela comuns, consulte WITH common_table_expression (Transact-SQL).
Sintaxe
WITH <NESTED_CTE_NAME_LEVEL1> [ (column_name , ...) ] AS
(WITH <NESTED_CTE_NAME_LEVEL2> [ (column_name , ...) ] AS
(
...
WITH <NESTED_CTE_NAME_LEVELn-1> [ ( column_name , ...) ] AS
(
WITH <NESTED_CTE_NAME_LEVELn> [ ( column_name , ...) ] AS
(
Standard_CTE_query_definition
)
<SELECT statement> -- Data source must include NESTED_CTE_NAME_LEVELn
)
<SELECT statement> -- Data source must include NESTED_CTE_NAME_LEVELn-1
...
)
<SELECT statement> -- Data source must include NESTED_CTE_NAME_LEVEL2
)
Diretrizes para criar e usar uma CTE aninhada
Além das diretrizes para criar e usar CTEs padrão, aqui estão diretrizes extras para CTEs aninhadas:
- Uma CTE aninhada só pode ser usada em uma instrução SELECT. Ele não pode ser usado em instruções UPDATE, INSERT ou DELETE.
- Nenhuma instrução UPDATE, INSERT ou DELETE é permitida na definição de um CTE aninhado.
- Os nomes CTE no mesmo nível de aninhamento não podem ser duplicados.
- Uma CTE aninhada só é visível para a CTE aninhada ou CTEs sequenciais que estão em seu nível mais alto imediato.
- Consultas entre bancos de dados são permitidas em uma definição de CTE aninhada.
- Dicas de consulta (ou seja, cláusula OPTION) não são permitidas na definição de CTE aninhada.
- O CTE aninhado não pode ser usado em CREATE VIEW.
- AS OF não é suportado na definição de um CTE aninhado.
- CTEs aninhados são suportados em uma definição de subconsulta CTE, mas não em uma subconsulta geral.
- Para melhor experiência, limite os níveis de nidificação a 64.
- O CTE aninhado pode ser criado no Editor de Consultas de Malha ou no SQL Server Management Studio (SSMS). O intellisense do SSMS não reconhece a sintaxe CTE aninhada, mas isso não bloqueia a criação de CTE aninhada.
Exemplos
Diferenças entre CTEs padrão, sequenciais e aninhadas
-- Standard CTE
;WITH Standard_CTE AS (
SELECT * FROM T1
)
SELECT * FROM Standard_CTE;
-- Sequential CTE
;WITH CTE1 AS (
SELECT * FROM T1
),
CTE2 AS (SELECT * FROM CTE1),
CTE3 AS (SELECT * FROM CTE2)
SELECT * FROM CTE3
-- Nested CTE
;WITH OUTER_CTE AS (
WITH INNER_CTE AS (
SELECT * FROM T1
)
SELECT * FROM INNER_CTE
)
SELECT * FROM OUTER_CTE;
O âmbito do nome do CTE é restrito ao seu âmbito
Os nomes CTE podem ser reutilizados em diferentes níveis de nidificação. Os nomes CTE no mesmo nível de aninhamento não podem ser duplicados. Neste exemplo, o nome cte1 é usado no escopo externo e interno.
;WITH
cte1 AS (
WITH
inner_cte1_1 AS (
SELECT * FROM NestedCTE_t1 WHERE c1 = 1
),
inner_cte1_2 AS (
SELECT * FROM inner_cte1_1 WHERE c2 = 1
)
SELECT * FROM inner_cte1_2
),
cte2 AS (
WITH
cte1 AS (
SELECT * FROM NestedCTE_t1 WHERE c3 = 1
),
inner_cte2_2 AS (
SELECT * FROM cte1 WHERE c4 = 1
)
SELECT * FROM inner_cte2_2
)
CTE aninhado complexo com união, união toda, interseção e exceto
CREATE TABLE NestedCTE_t1 (
c1 INT,
c2 INT,
c3 INT
);
GO
INSERT INTO NestedCTE_t1
VALUES (1, 1, 1);
INSERT INTO NestedCTE_t1
VALUES (2, 2, 2);
INSERT INTO NestedCTE_t1
VALUES (3, 3, 3);
GO
WITH
outermost_cte_1 AS (
WITH
outer_cte_1 AS (
WITH
inner_cte1_1 AS (
SELECT * FROM NestedCTE_t1 WHERE c1 = 1
),
inner_cte1_2 AS (
SELECT * FROM inner_cte1_1
UNION SELECT * FROM inner_cte1_1
)
SELECT * FROM inner_cte1_1
UNION ALL SELECT * FROM inner_cte1_2
),
outer_cte_2 AS (
WITH
inner_cte2_1 AS (
SELECT * FROM NestedCTE_t1 WHERE c2 = 1
EXCEPT SELECT * FROM outer_cte_1
),
inner_cte2_2 AS (
SELECT * FROM NestedCTE_t1 WHERE c3 = 1
UNION SELECT * FROM inner_cte2_1
)
SELECT * FROM inner_cte2_1
UNION ALL SELECT * FROM outer_cte_1
)
SELECT * FROM outer_cte_1
INTERSECT SELECT * FROM outer_cte_2
),
outermost_cte_2 AS (
SELECT * FROM outermost_cte_1
UNION SELECT * FROM outermost_cte_1
)
SELECT * FROM outermost_cte_1
UNION ALL SELECT * FROM outermost_cte_2;
O CTE aninhado é suportado na definição de subconsulta CTE, mas não na subconsulta geral
Esta consulta falha com o seguinte erro: Msg 156, Level 15, State 1, Line 3. Incorrect syntax near the keyword 'WITH'.
SELECT * FROM
(
WITH
inner_cte1_1 AS (SELECT * FROM NestedCTE_t1 WHERE c1 = 1),
inner_cte1_2 AS (SELECT * FROM inner_cte1_1)
SELECT * FROM inner_cte1_2
) AS subq1;
As referências a um CTE não podem exceder o seu âmbito
Esta consulta falha com o seguinte erro: Msg 208, Level 16, State 1, Line 1. Invalid object name 'inner_cte1_1'.
;WITH
outer_cte_1 AS (
WITH
inner_cte1_1 AS (
SELECT * FROM NestedCTE_t1 WHERE c1 = 1
),
inner_cte1_2 AS (
SELECT * FROM inner_cte1_1 WHERE c2 = 1
)
SELECT * FROM inner_cte1_2
),
outer_cte_2 AS (
WITH inner_cte2_1 AS (
SELECT * FROM NestedCTE_t1 WHERE c3 = 1
)
SELECT
tmp2.*
FROM
inner_cte1_1 AS tmp1,
inner_cte2_1 AS tmp2
WHERE
tmp1.c4 = tmp2.c4
)
SELECT * FROM outer_cte_2;