Блог

У меня есть таблица MySQL, которая выглядит примерно так:

 ----------------------
| ID | Name | Parent |
----------------------
| 1  |  a   | null   |
| 2  |  b   | null   |
| 3  |  c   | 1      |
| 4  |  d   | 3      |
| 5  |  e   | 2      |
| 6  |  f   | 2      |
----------------------
  

с неизвестным числом возможной глубины отношения родитель / потомок.

Я хочу запрос, который даст мне следующий результат:

 -----------------
| ID | Children |
-----------------
| 1  | 3,4      | -- because 4 is a child of 3, and 3 is a child of 1, it should show in both
| 2  | 5,6      |
| 3  | 4        |
| 4  | null     |
| 5  | null     |
| 6  | null     |
-----------------
  

Возможно ли получить такой результат в запросе? Я пробовал все, что я знаю, чтобы попробовать, и искал везде и не нашел ничего, что дало бы мне этот результат.

Рассматривая таблицу как employees с полями ID, Name, Parent .

Подход 1: Когда мы знаем глубину нашей иерархии, мы можем просто объединить наше табличное n время, равное нашей иерархии, и можем использовать GROUP BY для получения желаемых результатов. Здесь это 3 так

 SELECT t1.ID AS lev1,  GROUP_CONCAT(CONCAT_WS(',', t2.ID, t3.ID)) AS childs
FROM employees AS t1
LEFT JOIN employees AS t2 ON t2.Parent = t1.ID
LEFT JOIN employees AS t3 ON t3.Parent = t2.ID
GROUP BY t1.ID
  

Соответствующую скрипку вы можете посмотреть здесь.

Подход 2: когда мы не знаем глубину нашей иерархии

Для этого мы создадим две процедуры.

1. store_emp_childs — Он сохранит родительский элемент и его дочерний элемент во временной таблице.
2. get_emp_child — Он создаст временную таблицу, вызовет store_emp_childs для генерации результирующего набора. Выберите (верните) данные из временной таблицы и удалите временную таблицу.

 CREATE DEFINER=`root`@`localhost` PROCEDURE `get_emp_child`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

    CREATE TEMPORARY  TABLE `tmp_emp_child` (
        `emp_id` INT(11) NOT NULL,
        `child_id` INT(11) NOT NULL,
        PRIMARY KEY (`emp_id`, `child_id`)
    );
    
    CALL store_emp_childs(NULL, '');
    
    SELECT e.ID, GROUP_CONCAT(ec.child_id) AS childs
    FROM employees e
    LEFT JOIN tmp_emp_child ec ON e.ID = ec.emp_id
    GROUP BY e.ID;
    
    DROP TEMPORARY TABLE tmp_emp_child;
END
  

 CREATE DEFINER=`root`@`localhost` PROCEDURE `store_emp_childs`(
    IN `int_parent` INT,
    IN `old_parents` VARCHAR(100)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE finished, current_id INTEGER DEFAULT 0;
    DEClARE cur CURSOR FOR SELECT id FROM employees WHERE IFNULL(Parent, 0) = IFNULL(int_parent, 0);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

    OPEN cur;

    curID: LOOP
        FETCH cur INTO current_id;
        IF finished = 1 THEN 
            LEAVE curID;
        END IF;
        
        INSERT INTO tmp_emp_child (emp_id, child_id)
        SELECT id, current_id FROM employees WHERE FIND_IN_SET(id, old_parents) OR id = int_parent
        ON DUPLICATE KEY UPDATE emp_id = emp_id;
        
        CALL store_emp_childs(current_id, CONCAT(old_parents, ',', current_id));
    END LOOP curID;
    CLOSE cur;
END
  

Примечание:

1. Мы рекурсивно вызываем наш store_emp_childs . Для этого требуется max_sp_recursion_depth , чтобы параметр был установлен больше 0. Я рекомендую сделать это 250. Это не сработает, если записи больше, чем эта глубина рекурсии. Будем смотреть дальше, чтобы улучшить это.
2. Мы создаем временную таблицу, поэтому у пользователя должны быть права на ее создание.