MySQL实现树形查询

需要mysql 8以上，5.7不支持 

 

https://www.jianshu.com/p/f908aa35d448

 

	CREATE TABLE `perm_resource` (
  `resource_id` varchar(50) NOT NULL COMMENT '资源ID',
  `resource_name` varchar(100) DEFAULT NULL COMMENT '资源名称',
  `resource_parent_id` varchar(50) DEFAULT NULL COMMENT '父节点ID',
  `is_enable` varchar(1) DEFAULT NULL COMMENT '是否启用',
  `order_id` varchar(20) DEFAULT NULL COMMENT '排序ID',
  `resource_path` varchar(200) DEFAULT NULL COMMENT '资源路径',
  `menu_level` varchar(2) DEFAULT NULL COMMENT '菜单级别',
  `belong_systems` varchar(20) DEFAULT NULL COMMENT '所属系统',
  `is_del` varchar(1) DEFAULT NULL COMMENT '是否可删除',
  `resource_type` varchar(1) DEFAULT NULL COMMENT '资源类型(COMM_CODE_ZYLX)',
  `button_id` varchar(50) DEFAULT NULL COMMENT '功能菜单ID',
  `menu_path` varchar(200) DEFAULT NULL COMMENT '树结构路径',
  `login_flag` varchar(1) DEFAULT NULL COMMENT '是否需要登录标志',
  `login_page` varchar(200) DEFAULT NULL COMMENT '登录页面',
  `color` varchar(20) DEFAULT NULL COMMENT '按钮背景颜色',
  `icon` varchar(50) DEFAULT NULL COMMENT '图标资源',
  PRIMARY KEY (`resource_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='资源';


	INSERT INTO `perm_resource` VALUES ('011', '社会保险（个人）', '1', '1', '1', null, '2', '1', '1', null, null, '.1.011.', '1', null, null, null);
INSERT INTO `perm_resource` VALUES ('01101', '个人中心', '011', '1', '10', null, '3', '1', '1', '1', null, '.1.011.01101.', '1', '1', null, null);
INSERT INTO `perm_resource` VALUES ('0110102', '待遇资格', '01101', '1', '2', '/aio/view/neuqsoft/handan/yl/facecompare.jsp', '4', '1', '1', '1', null, '.1.011.01101.0110102.', '1', '1', '#4cd964', '/aio/aio/comm/image/menuIcon10.png');
INSERT INTO `perm_resource` VALUES ('0110103', '缴费证明打印', '01101', '1', '1', '/aio/view/neuqsoft/handan/yl/grjfzm.jsp', '4', '1', '1', '1', null, '.1.011.01101.0110103.', '1', '1', '#ff2d55', '/aio/aio/comm/image/menuIcon3.png');
INSERT INTO `perm_resource` VALUES ('01103', '医疗保险', '011', '1', '30', null, '3', '1', '1', '1', null, '.1.011.01103.', '1', '1', null, null);
INSERT INTO `perm_resource` VALUES ('0110307', '基本信息查询', '01103', '1', '10', '/aio/view/neuqsoft/handan/yb/query_basicinfo.jsp', '4', '1', '1', '2', null, '.1.011.01103.0110307.', '1', '1', '#5ac8fa', '/aio/aio/comm/image/menuIcon14.png');
INSERT INTO `perm_resource` VALUES ('0110308', '参保信息查询', '01103', '1', '20', '/aio/view/neuqsoft/handan/yb/grcbxxquery.jsp', '4', '1', '1', '2', null, '.1.011.01103.0110308.', '1', '1', null, '/aio/aio/comm/image/menuIcon1.png');
INSERT INTO `perm_resource` VALUES ('0110309', '缴费明细查询', '01103', '1', '30', '/aio/view/neuqsoft/handan/yb/query_jfmx.jsp', '4', '1', '1', '1', null, '.1.011.01103.0110309.', '1', '1', '#FF9900', '/aio/aio/comm/image/menuIcon12.png');
INSERT INTO `perm_resource` VALUES ('0110310', '医保账户查询', '01103', '1', '40', '/aio/view/neuqsoft/handan/yb/query_grzh.jsp', '4', '1', '1', '2', null, '.1.011.01103.0110310.', '1', '1', null, '/aio/aio/comm/image/menuIcon17.png');
INSERT INTO `perm_resource` VALUES ('0110311', '医疗消费查询', '01103', '1', '50', '/aio/view/neuqsoft/handan/yb/querydoctor_zhzc.jsp', '4', '1', '1', '2', null, '.1.011.01103.0110311.', '1', '1', null, '/aio/aio/comm/image/menuIcon16.png');
INSERT INTO `perm_resource` VALUES ('0110312', '药品目录', '01103', '1', '60', '/aio/view/neuqsoft/handan/yb/drugscatalog.jsp', '4', '1', '1', '2', null, '.1.011.01103.0110312.', '1', '1', null, '/aio/aio/comm/image/menuIcon4.png');
INSERT INTO `perm_resource` VALUES ('0110313', '诊疗目录', '01103', '1', '70', '/aio/view/neuqsoft/handan/yb/zhenliaomulu.jsp', '4', '1', '1', '2', null, '.1.011.01103.0110313.', '1', '1', null, '/aio/aio/comm/image/menuIcon23.png');
INSERT INTO `perm_resource` VALUES ('0110314', '医疗机构', '01103', '1', '80', '/aio/view/neuqsoft/handan/yb/query_yljg.jsp', '4', '1', '1', '2', null, '.1.011.01103.0110314.', '1', '1', null, '/aio/aio/comm/image/menuIcon21.png');
INSERT INTO `perm_resource` VALUES ('01104', '工伤保险', '011', '1', '40', null, '3', '1', '1', '1', null, '.1.011.01104.', '1', '1', null, null);
INSERT INTO `perm_resource` VALUES ('0110401', '参保信息查询', '01104', '1', '20', '/aio/view/neuqsoft/handan/gs/grcbxxquery.jsp', '4', '1', '1', '2', null, '.1.011.01104.0110401.', '1', '1', '#ff9500', '/aio/aio/comm/image/menuIcon1.png');
INSERT INTO `perm_resource` VALUES ('0110402', '待遇发放查询', '01104', '1', '30', '/aio/view/neuqsoft/handan/gs/query_dyff.jsp', '4', '1', '1', '2', null, '.1.011.01104.0110402.', '1', '1', '#4cd964', '/aio/aio/comm/image/menuIcon22.png');
INSERT INTO `perm_resource` VALUES ('0110403', '基本信息查询', '01104', '1', '10', '/aio/view/neuqsoft/handan/gs/query_basicinfo.jsp', '4', '1', '1', '2', null, '.1.011.01104.0110403.', '1', '1', '#4cd964', '/aio/aio/comm/image/menuIcon14.png');
INSERT INTO `perm_resource` VALUES ('0110404', '缴费明细查询', '01104', '1', '40', '/aio/view/neuqsoft/handan/gs/query_jfmx.jsp', '4', '1', '1', '2', null, '.1.011.01104.0110404.', '1', '1', null, '/aio/aio/comm/image/menuIcon12.png');
INSERT INTO `perm_resource` VALUES ('01105', '企业养老保险', '011', '1', '20', null, '3', '1', '1', '1', null, '.1.011.01105.', '1', '1', null, null);
INSERT INTO `perm_resource` VALUES ('0110501', '灵活人员缴费核定', '01105', '1', '80', '/aio/view/neuqsoft/handan/yl/hdsb.jsp', '4', '1', '1', '2', null, '.1.011.01105.0110501.', '1', '1', null, '/aio/aio/comm/image/lhjfhd.png');
INSERT INTO `perm_resource` VALUES ('0110502', '个人权益单打印', '01105', '1', '85', '/aio/view/neuqsoft/handan/yl/grqyd.jsp', '4', '1', '1', '2', null, '.1.011.01105.0110502.', '1', '1', null, '/aio/aio/comm/image/menuIcon16.png');
INSERT INTO `perm_resource` VALUES ('0110503', '待遇发放查询', '01105', '1', '40', '/aio/view/neuqsoft/handan/yl/query_yljff.jsp', '4', '1', '1', '1', null, '.1.011.01105.0110503.', '1', '1', '#ff2d55', '/aio/aio/comm/image/menuIcon19.png');
INSERT INTO `perm_resource` VALUES ('0110504', '退休金证明信打印', '01105', '1', '90', '/aio/view/neuqsoft/handan/yl/txrygzzm.jsp', '4', '1', '1', '2', null, '.1.011.01105.0110504.', '1', '1', null, '/aio/aio/comm/image/menuIcon17.png');
INSERT INTO `perm_resource` VALUES ('0110505', '基本信息查询', '01105', '1', '10', '/aio/view/neuqsoft/handan/yl/query_basicinfo.jsp', '4', '1', '1', '2', null, '.1.011.01105.0110505.', '1', '1', '#ffcc00', '/aio/aio/comm/image/menuIcon14.png');
INSERT INTO `perm_resource` VALUES ('0110506', '参保信息查询', '01105', '1', '20', '/aio/view/neuqsoft/handan/yl/grcbxxquery.jsp', '4', '1', '1', '2', null, '.1.011.01105.0110506.', '1', '1', '#ff9500', '/aio/aio/comm/image/menuIcon1.png');
INSERT INTO `perm_resource` VALUES ('0110510', '养老账户查询', '01105', '1', '60', '/aio/view/neuqsoft/handan/yl/query_ylyzh.jsp', '4', '1', '1', '2', null, '.1.011.01105.0110510.', '1', '1', null, '/aio/aio/comm/image/menuIcon17.png');
INSERT INTO `perm_resource` VALUES ('0110511', '缴费明细查询', '01105', '1', '30', '/aio/view/neuqsoft/handan/yl/query_jfmx.jsp', '4', '1', '1', '2', null, '.1.011.01105.0110511.', '1', '1', null, '/aio/aio/comm/image/menuIcon12.png');
INSERT INTO `perm_resource` VALUES ('0110512', '缴费证明打印', '01105', '1', '70', '/aio/view/neuqsoft/handan/yl/grjfzm.jsp', '4', '1', '1', '2', null, '.1.011.01105.0110512.', '1', '1', null, '/aio/aio/comm/image/menuIcon3.png');

with recursive tab1(resource_id,resource_name,menu_level,resource_path,rn,orderstr) AS (
    SELECT resource_id,resource_name,menu_level,resource_path,
           @rn := 1 rn,
           cast(@rn AS CHAR) orderstr
    FROM perm_resource t0
    WHERE t0.belong_systems = '1' AND t0.resource_id='011'

    union all 

    SELECT t1.resource_id,t1.resource_name,t1.menu_level,t2.resource_path,
           @rn := @rn + 1 rn,
           concat(t2.orderstr, '-', @rn)
    FROM perm_resource t1, tab1 t2
    WHERE t1.belong_systems = '1' AND t1.resource_parent_id = t2.resource_id
)
SELECT t3.* FROM tab1 t3 
WHERE t3.rn > '0'
ORDER BY t3.orderstr;