Блог

Мой вопрос основан на следующей статье (таблица и функция hierarchy_connect_by_parent_eq_prior_id)http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql /

Давайте предположим, что таблица t_hierarchy имеет два дополнительных поля (помимо id и родительского) typ1(char) и time (int). поле typ1 может иметь два значения A и B. Моя цель — отобразить все дерево, как описано в статье, но мне нужно дополнительное поле в результате, которое отображает время текущего узла (если typ1 = B) и всех его потомков (если typ1 = B). Итак, мне нужна сумма времен всех потомков для определенного узла (включая его самого), когда typ1 = B.

У меня есть следующее решение, но оно слишком медленное:

основной запрос:

  SELECT  CONCAT(REPEAT('    ', level - 1), hi.id) AS treeitem, get_usertime_of_current_node_and_descendants(hi.id) as B_time,
        hierarchy_sys_connect_by_path('/', hi.id) AS path,
        parent, level
FROM    (
        SELECT  hierarchy_connect_by_parent_eq_prior_id(id) AS id,
                CAST(@level AS SIGNED) AS level
        FROM    (
                SELECT  @start_with := 0,
                        @id := @start_with,
                        @level := 0
                ) vars, t_hierarchy
        WHERE   @id IS NOT NULL
        ) ho
JOIN    t_hierarchy hi
ON      hi.id = ho.id
  

Функция get_usertime_of_current_node_and_descendants(ввод int):

     BEGIN
        DECLARE _id INT;
        DECLARE _desctime INT;
        DECLARE _nodetime INT;
        SET _id = input;

select COALESCE((select sum(time) from (
                SELECT   hi.id, time,typ1
                FROM    (
                        SELECT  hierarchy_connect_by_parent_eq_prior_id_2(id) AS id, @levela AS level
                        FROM    (
                                SELECT  @start_witha := _id,
                                        @ida := @start_witha,
                                        @levela := 0,
                                ) vars, t_hierarchy a
                        WHERE   @ida IS NOT NULL
                        ) ho
                JOIN    t_hierarchy hi
                ON      hi.id = ho.id
                ) q where typ1 = 'B'), 0) into _desctime;
select COALESCE((select time from t_hierarchy where id = _id and typ1='B'), 0) into _nodetime;
return _desctime   _nodetime;

END $$
  

Функция hierarchy_connect_by_parent_eq_prior_id_2 такая же, как в статье и как приведенная выше hierarchy_connect_by_parent_eq_prior_id, но у нее другие имена глобальных переменных, поэтому она не будет мешать тем, которые используются в основном запросе.

Вышеупомянутое решение работает по желанию, но оно слишком медленное (особенно при работе с большими наборами данных). Можете ли вы предложить лучшее решение или можете ли вы предложить, как улучшить запрос? Заранее благодарю вас за ваше время и помощь!

Я решил проблему, извлекая время потомков за пределами mysql (перед вставкой записей в таблицу).