锚点和成员具有并集的递归查询

我正在尝试执行递归查询，但锚点和成员 cte 都有一个并集。这可能吗？我得到的错误是

Recursive member of a common table expression 'mainMenu' has multiple recursive references.

联合的目的是允许菜单项按角色或个人可见。如果有更好的方法，我会洗耳恭听。蒂亚！

with 
mainMenu (...)
as (
    select ...
    from (
        -- role
        select ...
        from ....
            and parentID is null
        UNION
        --user
        select ...
        from ....
            and parentID is null
    )as cteAnchor
UNION ALL
    select ...
    from (
        -- role
        select ...
        from ....
            inner join  mainMenu on parentID = mainMenu.id
        UNION
        -- user
        select ...
        from ....
            inner join  mainMenu on parentID = mainMenu.id
    )as cteChildren
)

select 
   ...
from 
mainMenu

你不能参考mainMenu不止一次。这是因为您实际上有两个锚表达式，一个用于角色，一个用于用户。有两种方法可以解决这个问题。您可以将查询拆分为两个 CTE（一个用于角色，一个用于用户）。像这样：

with
roleMainMenu (...)
as
(
    -- role
    select ...
    from ....
        and parentID is null
    union all
    select ...
    from ....
        inner join  mainMenu on parentID = mainMenu.id
),
userMainMenu (...)
as
(
    -- user
    select ...
    from ....
        and parentID is null
    union all
    select ...
    from ....
        inner join  mainMenu on parentID = mainMenu.id
)
select * from roleMainMenu
union
select * from userMainMenu

或者您可以预先组合角色和用户锚表达式。我不知道获取子项的查询对于角色和用户菜单项是否通用，否则您可以使用具有角色和用户根项联合的锚表达式。

with mainMenu (...)
as
(
    select root.* from
    (
        -- role roots
        select .. from ... and parentID is null
        union
        -- user roots
        select .. from ... and parentID is null
    ) root
    union all
    select ... from root
    inner join mainMenu on parentID = mainMenu.id
)
select *
from mainMenu