mysql 8.0前递归查询

本文主要是介绍mysql 8.0前递归查询，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

建表

CREATE TABLE `department` (
    `dep_id` int unsigned NOT NULL AUTO_INCREMENT comment '公司框架表',
    `dep_name` varchar(32) DEFAULT NULL COMMENT '部门名称',
    `parent_id` int unsigned DEFAULT NULL comment '上级id',
    `dep_path` varchar(255) DEFAULT NULL comment '路径名称',
    `del_flag` tinyint DEFAULT '0' comment '是否删除表',
    `parent_flag` tinyint DEFAULT '0' comment '是否是父级别',
    `enabled` tinyint(1) DEFAULT '1' comment '是否启用',
    PRIMARY KEY (`dep_id`),
    key `dep_parent_id`(`parent_id`),
    CONSTRAINT `dep_parent_id` FOREIGN KEY(`parent_id`) 
    	REFERENCES `department`(`dep_id`) ON DELETE  SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB default charset=utf8 comment '公司架构表';

插入数据

INSERT INTO department (dep_id, dep_name, parent_id, dep_path, del_flag, parent_flag, enabled) 
VALUES (1, 'a0', null, '1', 0, 1, 1),
  (2, 'b0', 1, '1,2', 0, 1, 1),
  (3, 'b1', 1, '1,3', 0, 1, 1),
  (4, 'b2', 1, '1,4', 0, 1, 1),
  (5, 'c0', 2, '1,2,5', 0, 1, 1),
  (6, 'c1', 2, '1,2,6', 0, 1, 1),
  (7, 'c2', 2, '1,2,7', 0, 1, 1),
  (8, 'c3', 2, '1,2,8', 0, 0, 1),
  (9, 'c4', 2, '1,2,9', 0, 1, 1),
  (10, 'c5', 3, '1,3,10', 0, 1, 1),
  (11, 'c6', 3, '1,3,11', 0, 0, 1),
  (12, 'c7', 3, '1,3,12', 0, 0, 1),
  (13, 'c8', 4, '1,4,13', 0, 0, 1),
  (14, 'd0', 5, '1,2,5,14', 0, 0, 1),
  (15, 'd1', 5, '1,2,5,15', 0, 0, 1),
  (16, 'd2', 5, '1,2,5,16', 0, 0, 1),
  (17, 'd3', 5, '1,2,5,17', 0, 0, 1),
  (18, 'd4', 12, '1,3,12,18', 0, 0, 1),
  (19, 'd5', 12, '1,3,12,19', 0, 0, 1),
  (20, 'd6', 12, '1,3,12,20', 0, 0, 1),
  (21, 'd7', 5, '1,2,5,21', 0, 0, 1),
  (22, 'd8', 13, '1,4,13,22', 0, 0, 1),
  (23, 'd9', 9, '1,2,9,23', 0, 0, 1),
  (24, 'd10', 9, '1,2,9,24', 0, 0, 1),
  (25, 'd11', 7, '1,2,7,25', 0, 0, 1),
  (26, 'd12', 10, '1,3,10,26', 0, 0, 1);

查询

SELECT B.dep_id, B.dep_name, B.dep_path, A.LEVEL
FROM
    (SELECT  @ids AS _ids,
         ( SELECT @ids := GROUP_CONCAT(dep_id)
           FROM department
           WHERE FIND_IN_SET(parent_id, @ids))  AS cids, @l := @l + 1 AS LEVEL
     FROM department, (SELECT @ids := 5, @l := 0) C
     WHERE @ids IS NOT NULL
    ) A,
    department B
WHERE FIND_IN_SET(B.dep_id, A._ids);

这篇关于mysql 8.0前递归查询的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！