建表

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);