MySQL实现树形查询

需要mysql 8以上，5.7不支持 

 

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

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68

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;