CTE (Expressão de Tabela Comum) aninhada no data warehouse do Fabric (Transact-SQL)
Aplica-se a: 
ponto de extremidade de análise SQL e Warehouse 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 CTE padrão, sequencial, recursiva e aninhada .
- Uma CTE padrão não faz referência ou define outra CTE em sua definição.
- A definição de um CTE aninhado inclui a definição de outro 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 faz referência a si mesmo em sua definição.
O Fabric Warehouse e o endpoint de análise do SQL oferecem suporte a CTEs padrão, sequenciais e aninhadas (versão prévia). Embora as CTEs padrão e sequenciais estejam geralmente disponíveis no Microsoft Fabric, as CTEs aninhadas são atualmente um recurso de visualização.
Para obter mais informações sobre expressões de tabela comuns, consulte WITH common_table_expression (Transact-SQL).
Observação
Durante a versão prévia, a criação de CTE aninhada tem suporte apenas no SQL Server Management Studio (SSMS). O Intellisense no SSMS não reconhece a sintaxe CTE aninhada, mas isso não bloqueia a criação de CTE aninhada. Para obter a melhor experiência, limite os níveis de aninhamento a 64.
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 as 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 uma CTE aninhada.
- 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 superior 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 uma CTE aninhada.
- A CTE aninhada não pode ser usada em CREATE VIEW.
- AS OF não é suportado na definição de uma CTE aninhada.
- As CTEs aninhadas têm suporte em uma definição de subconsulta CTE, mas não em uma subconsulta geral.
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 escopo do nome da CTE é restrito ao seu escopo
Os nomes CTE podem ser reutilizados em diferentes níveis de aninhamento. 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 todas, 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;
A CTE aninhada é suportada na definição de subconsulta CTE, mas não na subconsulta geral
Essa 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 uma CTE não podem exceder seu escopo
Essa 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;