如何在SQL中存储递归CTE结果？含变量存储报错问询

这个问题在SQL Server里很常见，你之前尝试的写法之所以报错，是因为SQL Server不支持在赋值表达式的括号内直接定义CTE——CTE需要作为独立的表达式，紧跟在使用它的查询语句之前。下面是两种可行的解决方法：

方法一：先声明变量，再用CTE赋值 #

这是最标准、可读性最高的写法：

-- 先声明存储统计结果的标量变量
DECLARE @numberOfRecords INT;

-- 定义递归CTE
WITH MyCTE (ChildID, ParentID) AS (
    SELECT ID, ParentID 
    FROM MyTable 
    WHERE ID = 1
    UNION ALL
    SELECT MT.ID, MT.ParentID 
    FROM MyCTE Child 
    JOIN MyTable MT ON MT.ID = Child.ParentID 
    WHERE Child.ParentID IS NOT NULL
)
-- 将CTE的统计结果赋值给变量
SELECT @numberOfRecords = COUNT(*) 
FROM MyCTE 
OPTION (MAXRECURSION 20);

-- 可选：验证变量中的结果
SELECT @numberOfRecords AS NumberOfRecords;

方法二：用子查询包装CTE（不推荐，可读性较差） #

如果想尽量紧凑书写，也可以把CTE嵌套在子查询里，但这种写法不如第一种直观：

DECLARE @numberOfRecords INT;

SELECT @numberOfRecords = (
    SELECT COUNT(*)
    FROM (
        WITH MyCTE (ChildID, ParentID) AS (
            SELECT ID, ParentID 
            FROM MyTable 
            WHERE ID = 1
            UNION ALL
            SELECT MT.ID, MT.ParentID 
            FROM MyCTE Child 
            JOIN MyTable MT ON MT.ID = Child.ParentID 
            WHERE Child.ParentID IS NOT NULL
        )
        SELECT * FROM MyCTE
    ) AS CteResults
) OPTION (MAXRECURSION 20);

SELECT @numberOfRecords AS NumberOfRecords;

额外说明 #

如果你的需求是把CTE的整个结果集（而非单一统计值）存入变量，需要使用表变量，示例如下：

-- 声明表变量匹配CTE的结构
DECLARE @CTEResults TABLE (ChildID INT, ParentID INT);

WITH MyCTE (ChildID, ParentID) AS (
    SELECT ID, ParentID 
    FROM MyTable 
    WHERE ID = 1
    UNION ALL
    SELECT MT.ID, MT.ParentID 
    FROM MyCTE Child 
    JOIN MyTable MT ON MT.ID = Child.ParentID 
    WHERE Child.ParentID IS NOT NULL
)
-- 将CTE结果写入表变量
INSERT INTO @CTEResults
SELECT * FROM MyCTE 
OPTION (MAXRECURSION 20);

-- 查询表变量内容
SELECT * FROM @CTEResults;

内容的提问来源于stack exchange，提问作者Stephen Oberauer