Kapslat common table-uttryck (CTE) i infrastrukturdatalager (Transact-SQL)
gäller för:
SQL-analysslutpunkt och lager i Microsoft Fabric
Vanliga tabelluttryck (CTE) kan förenkla komplexa frågor genom att dekonstruera normalt komplexa frågor i återanvändbara block.
Det finns fyra typer av CTE, inklusive standard, sekventiella, rekursivaoch kapslade CTE.
- En standard-CTE refererar inte till eller definierar ingen annan CTE i dess definition.
- En kapslad CTE-definition omfattar att definiera en annan CTE.
- En sekventiell CTE-definition kan referera till en befintlig CTE men kan inte definiera en annan CTE.
- En rekursiv CTE refererar till sig själv i sin definition.
Både Fabric Warehouse- och SQL-analysslutpunkten stöder standard, sekventiellaoch kapslade CTE:er. Standard- och sekventiella CTE:er är allmänt tillgängliga i Microsoft Fabric, men kapslade CTE:er är för närvarande en förhandsversionsfunktion.
Mer information om vanliga tabelluttryck finns i WITH common_table_expression (Transact-SQL).
Syntax
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
)
Riktlinjer för att skapa och använda en kapslad CTE
Förutom riktlinjer för att skapa och använda standard-CTE:er finns här extra riktlinjer för kapslade CTE:er:
- En kapslad CTE kan bara användas i en SELECT-instruktion. Det kan inte användas i UPDATE-, INSERT- eller DELETE-instruktioner.
- Inga UPDATE-, INSERT- eller DELETE-instruktioner tillåts i definitionen av en kapslad CTE.
- CTE-namn på samma kapslingsnivå kan inte dupliceras.
- En kapslad CTE är bara synlig för kapslade CTE eller sekventiella CTE:er som ligger på den omedelbart högre nivån.
- Frågor mellan databaser tillåts i en kapslad CTE-definition.
- Frågetips (dvs. OPTION-sats) tillåts inte i en kapslad CTE-definition.
- Kapslad CTE kan inte användas i CREATE VIEW.
- AS OF stöds inte i definitionen av en kapslad CTE.
- Kapslade CTE:er stöds i en CTE-underfrågasdefinition, men inte i en allmän underfråga.
- För bästa möjliga upplevelse kan du begränsa kapslingsnivåerna till 64.
- Kapslad CTE kan skapas i Fabric Query Editor eller SQL Server Management Studio (SSMS). SSMS intellisense känner inte igen kapslad CTE-syntax, men det blockerar inte skapandet av kapslad CTE.
Exempel
Skillnader mellan standard, sekventiella och kapslade CTE:er
-- 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;
CTE:s namnomfång är begränsat till dess omfång
CTE-namn kan återanvändas på olika kapslingsnivåer. CTE-namn på samma kapslingsnivå kan inte dupliceras. I det här exemplet används namnet cte1 i både yttre och inre omfång.
;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
)
Komplex kapslad CTE med union, union all, intersect och except
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;
Kapslad CTE stöds i CTE-underfrågor men inte i allmän underfråga
Den här frågan misslyckas med följande fel: 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;
Referenser till en CTE får inte överskrida dess omfång
Den här frågan misslyckas med följande fel: 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;