Fabric 数据仓库中的嵌套公用表表达式 （CTE）（Transact-SQL）
适用于：
Microsoft Fabric 中的 SQL 分析终结点和仓库
通用表表达式（CTE）可以通过将通常复杂的查询分解为可重用块来简化复杂查询。
有四种类型的 CTE，包括 标准、 顺序、 递归和 嵌套 CTE。
- 标准 CTE 在其定义中不引用或定义另一个 CTE。
- 嵌套 CTE 的定义包括定义另一个 CTE。
- 顺序 CTE 的定义可以引用现有的 CTE，但不能定义另一个 CTE。
- 递归 CTE 在其定义中引用自身。
Fabric Warehouse 和 SQL 分析终结点都支持 标准、 顺序和 嵌套 CTE（预览版）。 虽然标准 CTE 和顺序 CTE 已在 Microsoft Fabric 中正式发布，但嵌套 CTE 目前为预览版功能。
有关常见表表达式的详细信息，请参阅 WITH common_table_expression （Transact-SQL）。
注意
在预览期间，SQL Server Management Studio（SSMS）仅支持创建嵌套 CTE。 SSMS 中的 Intellisense 无法识别嵌套 CTE 语法，但这不会阻止创建嵌套 CTE。 为获得最佳体验，请将嵌套级别限制为 64。
语法
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
)
创建和使用嵌套 CTE 指南
除了创建和使用标准 CTE 的准则外，下面是嵌套 CTE 的额外准则：
- 嵌套 CTE 只能在 SELECT 语句中使用。 不能在 UPDATE、INSERT 或 DELETE 语句中使用。
- 嵌套 CTE 的定义中不允许使用 UPDATE、INSERT 或 DELETE 语句。
- 不能复制同一嵌套级别的 CTE 名称。
- 嵌套 CTE 仅对位于其直接更高级别的嵌套 CTE 或顺序 CTE 可见。
- 嵌套 CTE 定义中允许跨数据库查询。
- 嵌套 CTE 的定义中不允许查询提示（即 OPTION 子句）。
- 嵌套 CTE 不能在 CREATE VIEW 中使用。
- 嵌套 CTE 的定义不支持 AS OF。
- CTE 子查询定义中支持嵌套 CTE，但在常规子查询中不支持嵌套 CTE。
示例
标准、顺序和嵌套 CTE 之间的差异
-- 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 的名称范围仅限于其范围
CTE 名称可以在不同的嵌套级别重复使用。 不能复制同一嵌套级别的 CTE 名称。 在此示例中，名称 cte1 用于外部和内部作用域。
;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
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;
CTE 子查询定义支持嵌套 CTE，但在常规子查询中不支持嵌套 CTE
此查询失败，并出现以下错误： 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;
对 CTE 的引用不能超过其范围
此查询失败，并出现以下错误： 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;