关于 MySQL 递归查询

最近遇到的面试题。

假设有如下数据表,现在需要递归查询 A 下面的所有部门。

CREATE TABLE `depts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL,
  `dept_name` varchar(11) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
INSERT INTO `depts` (`id`, `parent_id`, `dept_name`)
VALUES
    (1,0,'A'),
    (2,1,'A-1'),
    (3,1,'A-2'),
    (4,2,'A-1-1'),
    (5,3,'A-2-1'),
    (6,0,'B'),
    (7,6,'B-1'),
    (8,7,'B-1-1'),
    (9,8,'B-1-1-1'),
    (10,6,'B-2'),
    (11,10,'B-2-1'),
    (12,10,'B-2-2');

以前没有遇到过这样的需求,Google 之后得到这样的函数。

delimiter //
CREATE FUNCTION `get_children_set`(dept_name TEXT)
RETURNS TEXT
BEGIN
DECLARE child_list TEXT;
DECLARE child TEXT;
DECLARE root_id INT;

SELECT id INTO root_id
FROM depts
WHERE depts.dept_name=dept_name;

SET child_list = '';
SET child = cast(root_id as CHAR);

WHILE child IS NOT NULL DO
SET child_list = concat(child_list, ',', child);
SELECT group_concat(id) INTO child
FROM depts
WHERE FIND_IN_SET(parent_id, child) > 0;

END WHILE;
RETURN child_list;
END
//
delimiter ;

SELECT *
FROM depts
JOIN users ON depts.id=users.dept_id
WHERE FIND_IN_SET(depts.id, get_children_set('A'));

由于上了储存过程,对分表会有很大影响,现在又已知最大层级,改成用查询完成。

SELECT *
FROM users
WHERE dept_id IN (
  SELECT depts_0.id
  FROM depts AS depts_0
    LEFT JOIN depts depts_1 ON depts_1.id = depts_0.parent_id
    LEFT JOIN depts depts_2 ON depts_2.id = depts_1.parent_id
    LEFT JOIN depts depts_3 ON depts_3.id = depts_2.parent_id
  WHERE 6 IN (
    depts_0.id,
    depts_0.parent_id,
    depts_1.parent_id,
    depts_2.parent_id,
    depts_3.parent_id
  )
);