如何在SQL中存储递归CTE的结果?

If I have a recursive CTE like this, how can I store the result in a variable?

如果有这样的递归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
)
SELECT COUNT(*)
FROM MyCTE
OPTION (MAXRECURSION 20)

I've tried the following, but SSMS complains about the syntax in both options:

我已经尝试了下面的方法，但是ssm在两个选项中都抱怨语法:

DECLARE @numberOfRecords 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
)
SELECT COUNT(*)
FROM MyCTE
OPTION (MAXRECURSION 20))

and

和

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
)
DECLARE @numberOfRecords int = (
SELECT COUNT(*)
FROM MyCTE
OPTION (MAXRECURSION 20))

1 个解决方案

#1

You can declare the variable first and then assign it:

你可以先声明变量，然后再分配:

DECLARE @numberOfRecords 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
     )
SELECT @numberOfRecords = COUNT(*)
FROM MyCTE
OPTION (MAXRECURSION 20);