mysql - 应该选择从端点ID到其所有父对象的递归查询

ModuleUserPermission是我要查询的主表，从那里我可以获取特定用户的模块ID，在模块表中我可以通过菜单ID查询菜单表，这里的问题是模块表仅由端点菜单ID组成，我希望其所有父级以及

select *
            from [ModuleUserPermission] mup
             inner join Module ml on ml.Id = mup.Module_Id
            --inner join Menu m on m.Id = ml.Menu_Id and m.IsActive = 1
            where (NULLIF(@AspNetUsers_Id,'') IS NULL OR mup.AspNetUsers_Id = @AspNetUsers_Id)

解：
这就是我所做的，我们能以适当的方式做到吗？

DECLARE @TblTemp TABLE(
    Id int,
    ParentId int,
    Name nvarchar(max),
    Url nvarchar(max) NULL,
    SortOrder int,
    IconClass nvarchar(max) NULL,
    IsActive bit
);



;With temp as   (
            select distinct m.*
                from [ModuleUserPermission] mup
                inner join Module ml on ml.Id = mup.Module_Id
                inner join Menu m on m.Id = ml.Menu_Id and m.IsActive = 1
            where (NULLIF(@AspNetUsers_Id,'') IS NULL OR mup.AspNetUsers_Id = @AspNetUsers_Id)

        union all

        select  m.*
            from Menu m
            inner join temp r on r.ParentId = m.Id
        where m.IsActive = 1
     )
     INSERT INTO @TblTemp  select * from temp

;With relation (Id, IconClass,SortOrder, ParentId, Name,Url,IsActive, [Level], [OrderSequence])
    as
    (
        select  distinct   m.Id, m.IconClass,m.SortOrder, m.ParentId, m.Name,ISNULL(m.Url,'') Url,m.IsActive, 1, cast(m.Id as varchar(20))
        from @TblTemp m where m.ParentId = 0

         union all

        select p.Id, p.IconClass,p.SortOrder, p.ParentId,p.Name,p.Url,p.IsActive, r.[Level]+1, cast(r.orderSequence + '_' + cast(p.Id as varchar) as varchar(20))
        from @TblTemp p
        inner join relation r on p.ParentId = r.Id

     )
select * from relation order by OrderSequence

最佳答案

由于在ParentId列中描述了顶层菜单项和底层菜单项之间的关系，因此您需要将Menu表与其自身连接起来，如下所示：

select  coalesce(m2.Id, m1.Id), coalesce(m2.Name, m1.Name)
from    [ModuleUserPermission] mup
join    Module ml on ml.Id = mup.Module_Id
join    Menu m on m.Id = ml.Menu_Id and m.IsActive = 1
left join Menu m2 on m.Id = m2.ParentId and m2.IsActive = 1
where   (NULLIF(@AspNetUsers_Id,'') IS NULL OR mup.AspNetUsers_Id = @AspNetUsers_Id)

left join是因为顶级项目不会与任何其他项目合并，因此使用inner join会丢失它们。这样，您可以保留它们，但保留null，因此保留coalesce

id

mysql - 应该选择从端点ID到其所有父对象的递归查询