Oracle递归查询+将查询结果转换为树级结构(c#、Java实现)

描述：现有两个数据表base_menu1和base_menu2，存储菜单，其中id字段表示菜单的唯一id，parent_id存储该菜单父级菜单的id，用递归查询的方式将菜单按照层级关系查询出来。

1. SQL 

select * from (
     select id,name,parent_id
     from base_menu1
     union all
     select id,name,parent_id
     from base_menu2 )t
start with t.parent_id is null
connect by prior t.id=t.parent_id

• 对同一级别的兄弟节点进行排序，同时区分菜单是几级菜单。该怎样修改上边的sql？

select t.id,t.name,t.parent_id,
    case
        when level=1 then '一级菜单'
        when level=2 then '二级菜单'
        else '三级菜单'
    end as menu_level
 from (
     select id,name,parent_id
     from base_menu1
     union all
     select id,name,parent_id
     from base_menu2 
)t
start with t.parent_id is null
connect by prior t.id=t.parent_id
ORDER SIBLINGS BY t.id;

• 显示菜单的层级路径(SYS_CONNECT_BY_PATH(id, ','))

select t.id,t.name,t.parent_id,SYS_CONNECT_BY_PATH(id, ',') menuPath,
    case
        when level=1 then 1
        when level=2 then 2
        when level=3 then 3
        else 4
    end as menu_level
 from (
     select id,name,parent_id
     from base_menu1
     union all
     select id,name,parent_id
     from base_menu2 
)t
start with t.parent_id is null
connect by prior t.id=t.parent_id
ORDER SIBLINGS BY t.id;

• 递归查询添加where条件：将二级菜单中名称为“管理工具”的菜单以及它的所有下层菜单进行过滤，应该怎样添加where条件？

SELECT t.id, t.name, t.parent_id,
    CASE
        WHEN LEVEL = 1 THEN '一级菜单'
        WHEN LEVEL = 2 THEN '二级菜单'
        ELSE '三级菜单'
    END AS menu_level
FROM (
    SELECT id, name, parent_id
    FROM base_menu1
    UNION ALL
    SELECT id, name, parent_id
    FROM base_menu2
) t
START WITH t.parent_id IS NULL
CONNECT BY PRIOR t.id = t.parent_id
AND (LEVEL <= 2 OR (LEVEL = 3 AND t.name <> '管理工具'))
ORDER SIBLINGS BY t.id;

 注：

1. union all：表示两个表的查询结果合并

2. connect by prior和start with用来实现递归查询。start with指定递归的起始节点，connect by prior指定父子关系。

3. case when根据菜单的层级关系(level)向菜单添加level字段，使用该字段区分菜单的层级。

4. ORDER SIBLINGS BY：只能用于connect by执行的递归查询，用于在兄弟节点中执行排序，不影响父节点和子节点之间的排序。

5. SYS_CONNECT_BY_PATH 函数将菜单路径连接成一个路径字符串: 以便后续在代码中使用

6. AND (LEVEL <= 2 OR (LEVEL = 3 AND t.name <> '管理工具'))：

    6.1   LEVEL <= 2：保留一级和二级菜单。

    6.2   LEVEL = 3 AND t.name <> '管理工具'：限制第三级菜单，排除名称为“管理工具”的菜单及其下级菜单

2. 将菜单数据转化为树级结果

2.1 C# 实现

        使用递归的方式将查询结果转换为树级结构

//定义一个菜单类
public class Menu
{
    public string id { get; set; }
    public string name { get; set; }
    public string parentId{ get; set; }
    public List<Menu> child { get; set; }=new List<Menu>();
}

//递归构建菜单树
public static List<Menu> GetMenuTree(List<Menu> menu_tree)
{
    var lookUp = menu_tree.ToLookup(x => x.parentId);//创建分组字典，parentId为键
    var rootItems = lookUp[null].ToList();//根菜单
    //内部方法：递归构建每个菜单的子项
    void GetMenuChild(Menu parent)
    {
        parent.child.AddRange(lookUp[parent.id].ToList());//获取当前菜单的所有子项
        foreach (var child in parent.child)
        {
            GetMenuChild(child);
        }
    }
    foreach (var child in rootItems)
    {
        GetMenuChild(child);
    }
    return rootItems;
}

2.2 Java实现

@Service
public class BaseMenuOperation {
    public List<BaseMenu> getMenuTree(List<BaseMenu> menus)
    {
        //Map中的Key值为空会报错
        menus.stream().map(x->{
            if(x.getParentId()==null){
                x.setParentId("null");
            }
            return x;
        }).collect(Collectors.toList());

        //以parent_id进行分类
        Map<String,List<BaseMenu>> lookUp=menus.stream().collect(Collectors.groupingBy(BaseMenu::getParentId));

        //根节点
        List<BaseMenu> rootItems=lookUp.getOrDefault("null",new ArrayList<>());

        //建树
        for (BaseMenu parent:rootItems)
        {
            buildMenuChild(parent,lookUp);
        }
        return rootItems;
    }
    //获取子树
    public void buildMenuChild(BaseMenu parent,Map<String,List<BaseMenu>> lookup)
    {
        List<BaseMenu> childs=lookup.getOrDefault(parent.getId(),new ArrayList<>());
        parent.setChild(childs);
        for (BaseMenu child:childs)
        {
            buildMenuChild(child,lookup);
        }
    }
}