MySql-3-递归查询父级下的最子节点集合-直接上代码

1、构建实体

public class ProtalMenuTreeRespVO implements Serializable {
    private static final long serialVersionUID = 1735287763708L;
 
    @ApiModelProperty(value = "id（雪花）")
    private String id;
 
    @ApiModelProperty(value = "编码")
    private Integer menuCode;
 
    @ApiModelProperty(value = "按钮名称")
    private String name;
 
    @ApiModelProperty(value = "上级按钮id（最父级为0）")
    private String parentId;
 
}

2、构建表结构

CREATE TABLE `protal_menu` (
  `id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'id（雪花）',
  `name` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '按钮名称',
  `menu_code` int DEFAULT NULL COMMENT '按钮编码',
  `type` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '按钮类型（content-内容，link-链接）',
  `head_picture` json DEFAULT NULL COMMENT '按钮头图',
  `parent_id` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '父级id',
  `sort` int DEFAULT NULL COMMENT '顺序',
  `level` int DEFAULT '1' COMMENT '层级',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='栏目表';

3、递归查询父级下的最子节点集合

    <select id="getMenuTreeChildNodesByParentCode"         
                      resultType="com.e6yun.project.portal.vo.client.ChildNodeRespVO">
        WITH RECURSIVE menu_nodes AS (
                -- 基础查询：选择指定的父节点
                SELECT p.id, p.parent_id, p.name, p.menu_code
                FROM protal_menu p
                WHERE
                    p.valid = 1
                    and p.corp_id = #{param.corpId}
                    <if test="param.parentId != null and param.parentId != ''">
                        and p.id = #{param.parentId}
                    </if>
                    <if test="param.parentCode != null and param.parentCode != ''">
                        and p.menu_code = #{param.parentCode}
                    </if>
            UNION ALL
                -- 递归部分：选择当前子节点的所有子节点
                SELECT n.id, n.parent_id, n.name, n.menu_code
                FROM protal_menu n
                INNER JOIN menu_nodes sn ON n.parent_id = sn.id
                WHERE n.valid = 1
                    and n.corp_id = #{param.corpId}
        )
        -- 选择所有找到的子节点的最子节点
        SELECT DISTINCT id, parent_id, name, menu_code
        FROM menu_nodes
        WHERE id NOT IN (SELECT parent_id FROM menu_nodes WHERE parent_id != '0');
    </select>