sql - CTE 和结束分号

Question

我正在尝试编写一个递归 CTE，它引用另一个在它之前编写的 CTE。

第一个 cte nodes 在编写递归 Hierarchy cte 之前我用分号关闭了:

WITH nodes(node, node_name, parent, parent_name) AS
(
    SELECT      
        Grp.PermissionGroupID as node,
        Grp.GroupName as node_name,
        GrpLink.ParentPermissionGroupID as parent,
        ParentGrp.GroupName as parent_name
    FROM _CCC_Permission_Group Grp
    LEFT JOIN   _CCC_Permission_GroupGroup GrpLink
    on          Grp.PermissionGroupID = GrpLink.ChildPermissionGroupID
    LEFT JOIN   _CCC_Permission_Group ParentGrp
    on          GrpLink.ParentPermissionGroupID = ParentGrp.PermissionGroupID

);

WITH Hierarchy(node, node_name, depth, parent, parent_name)
AS
(
    SELECT      
        node,
        node_name,
        0,
        parent,
        parent_name
    FROM nodes as FirstDepth
    WHERE parent IS NULL

    UNION ALL

    SELECT
        NextDepth.node,
        NextDepth.node_name,
        Parent.depth + 1,
        Parent.parent,
        Parent.parent_name              
    FROM nodes as NextDepth
    INNER JOIN  Hierarchy as Parent
    on          NextDepth.parent = Parent.parent

)

SELECT      *
FROM        Hierarchy
OPTION      (MAXRECURSION 32767)

我得到错误:

Msg 102, Level 15, State 1, Line 17 Incorrect syntax near ';'.

当我删除分号时，出现错误:

Msg 156, Level 15, State 1, Line 19 Incorrect syntax near the keyword 'WITH'.

Msg 319, Level 15, State 1, Line 19 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

...我不经常编写 CTE(更不用说递归的了)，所以我不太确定如何解决与 ; 放置的这种明显冲突。

Answer 1

CTE 只需要一个 WITH 子句。您可以创建 stacked CTE，只需在前面的 CTE 末尾添加逗号，然后添加 CTE 名称

;WITH nodes(node, node_name, parent, parent_name) AS
(
....
),Hierarchy(node, node_name, depth, parent, parent_name)
AS
(
..
)
SELECT *
FROM   Hierarchy
OPTION (MAXRECURSION 32767)