Рекурсивные родители, использующие Joins и Group_Concats с основными таблицами и поисковыми таблицами [duplicate]

У меня возникла проблема, когда мне приходилось запускать компиляцию Maven в моем проекте из командной строки для запуска моих модульных тестов; если я внес изменения в тестовый класс и позволю Eclipse автоматически перекомпилировать его, тогда я получил ошибку «Unsupported major.minor version 51.0».

У меня есть как JDK6, так и JDK7, но все мои JRE настройки указывали на 1.6, как на pom, так и на странице свойств проекта в Eclipse.

Наконец, я попытался закрыть проект и снова открыть его, и это, казалось, исправило это! НТН

142
задан Robert Harvey 6 May 2015 в 16:33
поделиться

11 ответов

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

ancestor | descendant | depth
0        | 0          | 0
0        | 19         | 1
0        | 20         | 2
0        | 21         | 3
0        | 22         | 4
19       | 19         | 0
19       | 20         | 1
19       | 21         | 3
19       | 22         | 4
20       | 20         | 0
20       | 21         | 1
20       | 22         | 2
21       | 21         | 0
21       | 22         | 1
22       | 22         | 0

. После того, как у вас есть эта таблица, иерархические запросы становятся очень легкими и быстрыми. Чтобы получить всех потомков категории 20:

SELECT cat.* FROM categories_closure AS cl
INNER JOIN categories AS cat ON cat.id = cl.descendant
WHERE cl.ancestor = 20 AND cl.depth > 0

Конечно, существует большой недостаток, когда вы используете денормализованные данные, подобные этому. Вам нужно поддерживать таблицу закрытия рядом с таблицей категорий. Лучший способ, вероятно, использовать триггеры, но несколько сложно правильно отслеживать вставки / обновления / удаления для таблиц закрытия.

Редактирование: см. Вопрос Какие существуют варианты хранения иерархических данных в реляционной базе данных?

g0] для получения дополнительных опций. Существуют различные оптимальные решения для разных ситуаций.

68
ответ дан Community 21 August 2018 в 18:43
поделиться
  • 1
    Это нормально, если в иерархии не более 4 уровней. Если есть N уровней, вы должны знать, что правильно создать запрос. – Jonathan Leffler 26 November 2013 в 13:47
  • 2
    @Damodaran, Спасибо за ваш ответ ... Мне нужно было такое состояние, когда количество детей неизвестно ... и в блоге, который использует концепцию внутреннего соединения, в том, что требуется знать иерархию, которая не является в моем случае ... так что дайте мне знать ваше мнение по тому же ... Итак, простыми словами мне нужен запрос для обработки уровней n-hirerachy, где «n» неизвестно ..... – Tarun Parswani 26 November 2013 в 13:48
  • 3
    @ user3036105: это невозможно сделать в MySQL с помощью SQL-запроса single . Для этого MySQL просто недостаточно развит. Если вам это действительно нужно, подумайте о переходе на СУБД, которая поддерживает рекурсивные запросы. – a_horse_with_no_name 26 November 2013 в 14:40
  • 4
    & gt; Большинство пользователей в тот или иной момент имели дело с иерархическими данными в базе данных SQL и, несомненно, узнали, что управление иерархическими данными не является целью реляционной базы данных. Возможно, вы имели в виду базу данных MySQL. База данных Oracle обрабатывает иерархические данные и запросы достаточно хорошо. – Peter Nosko 18 September 2014 в 22:30
  • 5
    Как мы можем это сделать? SELECT idFolder, (SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM ( SELECT @pv:=(SELECT GROUP_CONCAT(idFolder SEPARATOR ',') FROM Folder WHERE idFolderParent IN (@pv)) AS lv FROM Folder JOIN (SELECT @pv:= F1.idFolder )tmp WHERE idFolderParent IN (@pv)) a) from folder F1 where id > 10; Я не могу ссылаться на F1.idFolder для @pv – Rahul 12 June 2015 в 01:34
  • 6
    «... управление иерархическими данными - это не то, что реляционная база данных предназначена для ...». Хотя это, возможно, не было первоначальным намерением реляционной базы данных, в реальных иерархических данных реального мира невероятно обыденно, и MySQL должен отражать, как люди действительно должны использовать свои данные в реальных сценариях. – Dave L 28 September 2016 в 14:57

Мне было легче:

1) создать функцию, которая будет проверять, находится ли элемент где-либо в родительской иерархии другого. Что-то вроде этого (я не буду писать функцию, сделаю ее с WHILE DO):

is_related(id, parent_id);

в вашем примере

is_related(21, 19) == 1;
is_related(20, 19) == 1;
is_related(21, 18) == 0;

2) используйте подвыбор, что-то например:

select ...
from table t
join table pt on pt.id in (select i.id from table i where is_related(t.id,i.id));
0
ответ дан cripox 21 August 2018 в 18:43
поделиться

Попробуйте следующее:

Определение таблицы:

DROP TABLE IF EXISTS category;
CREATE TABLE category (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20),
    parent_id INT,
    CONSTRAINT fk_category_parent FOREIGN KEY (parent_id)
    REFERENCES category (id)
) engine=innodb;

Экспериментальные строки:

INSERT INTO category VALUES
(19, 'category1', NULL),
(20, 'category2', 19),
(21, 'category3', 20),
(22, 'category4', 21),
(23, 'categoryA', 19),
(24, 'categoryB', 23),
(25, 'categoryC', 23),
(26, 'categoryD', 24);

Рекурсивная хранимая процедура:

DROP PROCEDURE IF EXISTS getpath;
DELIMITER $$
CREATE PROCEDURE getpath(IN cat_id INT, OUT path TEXT)
BEGIN
    DECLARE catname VARCHAR(20);
    DECLARE temppath TEXT;
    DECLARE tempparent INT;
    SET max_sp_recursion_depth = 255;
    SELECT name, parent_id FROM category WHERE id=cat_id INTO catname, tempparent;
    IF tempparent IS NULL
    THEN
        SET path = catname;
    ELSE
        CALL getpath(tempparent, temppath);
        SET path = CONCAT(temppath, '/', catname);
    END IF;
END$$
DELIMITER ;

Функция обертки для хранимой процедуры:

DROP FUNCTION IF EXISTS getpath;
DELIMITER $$
CREATE FUNCTION getpath(cat_id INT) RETURNS TEXT DETERMINISTIC
BEGIN
    DECLARE res TEXT;
    CALL getpath(cat_id, res);
    RETURN res;
END$$
DELIMITER ;

Выберите пример:

SELECT id, name, getpath(id) AS path FROM category;

Выход:

+----+-----------+-----------------------------------------+
| id | name      | path                                    |
+----+-----------+-----------------------------------------+
| 19 | category1 | category1                               |
| 20 | category2 | category1/category2                     |
| 21 | category3 | category1/category2/category3           |
| 22 | category4 | category1/category2/category3/category4 |
| 23 | categoryA | category1/categoryA                     |
| 24 | categoryB | category1/categoryA/categoryB           |
| 25 | categoryC | category1/categoryA/categoryC           |
| 26 | categoryD | category1/categoryA/categoryB/categoryD |
+----+-----------+-----------------------------------------+

Фильтрация строк определенным путем :

SELECT id, name, getpath(id) AS path FROM category HAVING path LIKE 'category1/category2%';

Выход:

+----+-----------+-----------------------------------------+
| id | name      | path                                    |
+----+-----------+-----------------------------------------+
| 20 | category2 | category1/category2                     |
| 21 | category3 | category1/category2/category3           |
| 22 | category4 | category1/category2/category3/category4 |
+----+-----------+-----------------------------------------+
7
ответ дан Fandi Susanto 21 August 2018 в 18:43
поделиться
  • 1
    Это не будет работать более одного ребенка. например (20, 'category2', 19), (21, 'category3', 20), (22, 'category4', 20), – Basheer Ahmed Kharoti 3 April 2018 в 06:54
  • 2
    Я уверен, что это работает для более чем одного ребенка. Я даже проверил его снова. – Fandi Susanto 7 April 2018 в 14:12

Лучший способ, с которым я столкнулся, -

  1. Использовать lineage для хранения \ sort \ trace trees. Этого более чем достаточно и работает в тысячу раз быстрее, чем любой другой подход. Он также позволяет оставаться на этом шаблоне, даже если DB изменится (поскольку ANY db позволит использовать этот шаблон)
  2. Использовать функцию, определяющую линию для конкретного идентификатора.
  3. Использовать как вы хотите (в выборе или в CUD-операциях или даже по заданию).

Линейный подход descr. можно найти там, где, например, Здесь или здесь . Что касается функции - , что - это то, что у меня получилось.

В конце - получилось более или менее простое, относительно быстрое и простое решение.

Тело функции

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `get_lineage`(the_id INT) RETURNS text CHARSET utf8
    READS SQL DATA
BEGIN

 DECLARE v_rec INT DEFAULT 0;

 DECLARE done INT DEFAULT FALSE;
 DECLARE v_res text DEFAULT '';
 DECLARE v_papa int;
 DECLARE v_papa_papa int DEFAULT -1;
 DECLARE csr CURSOR FOR 
  select _id,parent_id -- @n:=@n+1 as rownum,T1.* 
  from 
    (SELECT @r AS _id,
        (SELECT @r := table_parent_id FROM table WHERE table_id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := the_id, @l := 0,@n:=0) vars,
        table m
    WHERE @r <> 0
    ) T1
    where T1.parent_id is not null
 ORDER BY T1.lvl DESC;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    open csr;
    read_loop: LOOP
    fetch csr into v_papa,v_papa_papa;
        SET v_rec = v_rec+1;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- add first
        IF v_rec = 1 THEN
            SET v_res = v_papa_papa;
        END IF;
        SET v_res = CONCAT(v_res,'-',v_papa);
    END LOOP;
    close csr;
    return v_res;
END

И тогда вы просто

select get_lineage(the_id)

Надеюсь, это поможет кому-то:)

7
ответ дан Kamil G. 21 August 2018 в 18:43
поделиться

Простой запрос для списка первой рекурсии:

select @pv:=id as id, name, parent_id
from products
join (select @pv:=19)tmp
where parent_id=@pv

Результат:

id  name        parent_id
20  category2   19
21  category3   20
22  category4   21
26  category24  22

... с левым соединением:

select
    @pv:=p1.id as id
  , p2.name as parent_name
  , p1.name name
  , p1.parent_id
from products p1
join (select @pv:=19)tmp
left join products p2 on p2.id=p1.parent_id -- optional join to get parent name
where p1.parent_id=@pv

Решение @tincot для перечисления всех дочерних элементов:

select  id,
        name,
        parent_id 
from    (select * from products
         order by parent_id, id) products_sorted,
        (select @pv := '19') initialisation
where   find_in_set(parent_id, @pv) > 0
and     @pv := concat(@pv, ',', id)

Проверьте его онлайн с помощью Sql Fiddle и просмотрите все результаты.

http : //sqlfiddle.com/# 9 / a318e3 / 4/0

3
ответ дан lynx_74 21 August 2018 в 18:43
поделиться

Я сделал запрос для вас. Это даст вам рекурсивную категорию с одним запросом:

SELECT id,NAME,'' AS subName,'' AS subsubName,'' AS subsubsubName FROM Table1 WHERE prent is NULL
UNION 
SELECT b.id,a.name,b.name AS subName,'' AS subsubName,'' AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id WHERE a.prent is NULL AND b.name IS NOT NULL 
UNION 
SELECT c.id,a.name,b.name AS subName,c.name AS subsubName,'' AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id LEFT JOIN Table1 AS c ON c.prent=b.id WHERE a.prent is NULL AND c.name IS NOT NULL 
UNION 
SELECT d.id,a.name,b.name AS subName,c.name AS subsubName,d.name AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id LEFT JOIN Table1 AS c ON c.prent=b.id LEFT JOIN Table1 AS d ON d.prent=c.id WHERE a.prent is NULL AND d.name IS NOT NULL 
ORDER BY NAME,subName,subsubName,subsubsubName

Вот скрипка .

-1
ответ дан Manish 21 August 2018 в 18:43
поделиться

Что-то, о чем здесь не упоминалось, хотя и немного похоже на вторую альтернативу принятого ответа, но отличается и невысокой стоимостью для большого иерархического запроса и простых элементов (insert update delete), будет добавлять столбец постоянных путей для каждого элемента.

some like:

id | name        | path
19 | category1   | /19
20 | category2   | /19/20
21 | category3   | /19/20/21
22 | category4   | /19/20/21/22

Пример:

-- get children of category3:
SELECT * FROM my_table WHERE path LIKE '/19/20/21%'
-- Reparent an item:
UPDATE my_table SET path = REPLACE(path, '/19/20', '/15/16') WHERE path LIKE '/19/20/%'

Оптимизировать длину пути и ORDER BY path, используя кодировку base36, вместо этого действительный числовой идентификатор пути

]
 // base10 => base36
 '1' => '1',
 '10' => 'A',
 '100' => '2S',
 '1000' => 'RS',
 '10000' => '7PS',
 '100000' => '255S',
 '1000000' => 'LFLS',
 '1000000000' => 'GJDGXS',
 '1000000000000' => 'CRE66I9S'

https://en.wikipedia.org/wiki/Base36

Подавление разделителя '/' с помощью фиксированной длины и отступов до encoded id

Подробное объяснение оптимизации здесь: https://bojanz.wordpress.com/2014/04/25/storing-hierarchical-data-materialized-path/

TODO

построение функции или процедуры для разделения пути для возвращающих предков одного элемента

0
ответ дан MTK 21 August 2018 в 18:43
поделиться

Вы можете легко сделать это в других базах данных с помощью рекурсивного запроса (YMMV на производительность).

Другой способ сделать это - сохранить два дополнительных бита данных, левый и правый стоимость. Левое и правое значение получены из предварительного предпросмотра древовидной структуры, которую вы представляете.

Это известно как измененный обход дерева предзаказов и позволяет запускать простой запрос, чтобы получить все родительские значения в один раз. Он также имеет название «вложенный набор».

3
ответ дан Phil John 21 August 2018 в 18:43
поделиться

Просто используйте класс BlueM / tree php для создания дерева таблицы самосогласования в mysql.

Tree и Tree\node - это классы PHP для обработки данных который структурирован иерархически с использованием ссылок на родительские идентификаторы. Типичным примером является таблица в реляционной базе данных, где «родительское» поле каждой записи ссылается на первичный ключ другой записи. Конечно, Tree может не только использовать данные, происходящие из базы данных, но и все: вы предоставляете данные, а Tree использует их независимо от того, откуда поступают данные и как они были обработаны. подробнее

Вот пример использования BlueM / tree:

<?php 
require '/path/to/vendor/autoload.php'; $db = new PDO(...); // Set up your database connection 
$stm = $db->query('SELECT id, parent, title FROM tablename ORDER BY title'); 
$records = $stm->fetchAll(PDO::FETCH_ASSOC); 
$tree = new BlueM\Tree($records); 
...
1
ответ дан Saleh Mosleh 21 August 2018 в 18:43
поделиться

Его немного сложно, проверьте, работает ли он для вас

select a.id,if(a.parent = 0,@varw:=concat(a.id,','),@varw:=concat(a.id,',',@varw)) as list from (select * from recursivejoin order by if(parent=0,id,parent) asc) a left join recursivejoin b on (a.id = b.parent),(select @varw:='') as c  having list like '%19,%';

Ссылка на скрипт SQL http://www.sqlfiddle.com/#!2/e3cdf/2

Замените имя поля и таблицы соответствующим образом.

1
ответ дан senK 21 August 2018 в 18:43
поделиться
  • 1
    В этом случае это не будет работать sqlfiddle.com / #! 2/19360/2 , с этим трюком, по крайней мере, сначала вы должны заказать иерархический уровень. – Jaugar Chang 12 September 2014 в 13:34

Для версий MySql, которые не поддерживают Common Table Expressions (до версии 5.7), вы достигнете этого следующим запросом:

select  id,
        name,
        parent_id 
from    (select * from products
         order by parent_id, id) products_sorted,
        (select @pv := '19') initialisation
where   find_in_set(parent_id, @pv)
and     length(@pv := concat(@pv, ',', id))

Здесь скрипт .

Значение, указанное в @pv := '19', должно быть установлено на id родителя, которому вы хотите выбрать всех потомков.

Это будет работать также, если родитель имеет несколько детей. Тем не менее, требуется, чтобы каждая запись выполняла условие parent_id < id, в противном случае результаты не будут выполнены.

Этот запрос использует определенный синтаксис MySql: переменные назначаются и изменяются во время его выполнения. Некоторые предположения сделаны о порядке выполнения:

  • Сначала оценивается предложение from. Таким образом, здесь инициализируется @pv.
  • Предложение where оценивается для каждой записи в порядке извлечения из псевдонимов from. Таким образом, это условие включает в себя только те записи, для которых родительский элемент уже был идентифицирован как находящийся в дереве потомков (все потомки основного родителя постепенно добавляются к @pv).
  • Условия в этом предложении where оцениваются по порядку, и оценка прерывается после того, как общий результат определен. Поэтому второе условие должно быть на втором месте, так как оно добавляет id в родительский список, и это должно произойти только в том случае, если id передает первое условие. Функция length вызывается только для того, чтобы убедиться, что это условие всегда истинно, даже если строка pv по какой-то причине дает значение фальши.

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

Также обратите внимание, что для очень больших наборов данных это решение может замедляться, поскольку операция find_in_set не является идеальным способом найти число в списке, но не в списке, который достигает размера в том же порядке, что и количество возвращенных записей.

Альтернатива 1: WITH RECURSIVE, CONNECT BY

Все больше и больше баз данных реализуют SQL: 1999 ISO стандартный WITH [RECURSIVE] синтаксис для рекурсивных запросов (например, Postgres 8.4 + , SQL Server 2005 + , DB2 , Oracle 11gR2 + , SQLite 3.8.4 + , Firebird 2.1 + , H2 , HyperSQL 2.1.0 + , Teradata , MariaDB 10.2.2 + ). А с версии 8.0 также поддерживает MySql . С этим синтаксисом запрос выглядит следующим образом:

with recursive cte (id, name, parent_id) as
(
 select     id,
            name,
            parent_id
 from       products
 where      parent_id = 19
 union all
 select     p.id,
            p.name,
            p.parent_id
 from       products p
 inner join cte
         on p.parent_id = cte.id
)
select * from cte;

В некоторых базах данных есть альтернативный, нестандартный синтаксис для иерархических поисков, таких как предложение CONNECT BY в базах данных Oracle. DB2 также поддерживает этот альтернативный синтаксис.

MySql версия 5.7 не предлагает такую ​​функцию. Когда ваш механизм базы данных предоставляет этот синтаксис, то это, безусловно, лучший вариант. Если нет, то также рассмотрите следующие альтернативы.

Альтернатива 2: Идентификаторы стиля пути

Все становится намного проще, если вы присвойте значения id, которые содержат иерархическую информацию: путь. Например, в вашем случае это может выглядеть так:

ID       | NAME
19       | category1   
19/1     | category2  
19/1/1   | category3  
19/1/1/1 | category4  

Тогда ваш select будет выглядеть так:

select  id,
        name 
from    products
where   id like '19/%'

Альтернатива 3: Повторные самосоединения

Если вы знаете верхний предел того, насколько глубоким может стать ваше дерево иерархии, вы можете использовать стандартный sql, например:

select      p6.parent_id as parent6_id,
            p5.parent_id as parent5_id,
            p4.parent_id as parent4_id,
            p3.parent_id as parent3_id,
            p2.parent_id as parent2_id,
            p1.parent_id as parent_id,
            p1.id as product_id,
            p1.name
from        products p1
left join   products p2 on p2.id = p1.parent_id 
left join   products p3 on p3.id = p2.parent_id 
left join   products p4 on p4.id = p3.parent_id  
left join   products p5 on p5.id = p4.parent_id  
left join   products p6 on p6.id = p5.parent_id
where       19 in (p1.parent_id, 
                   p2.parent_id, 
                   p3.parent_id, 
                   p4.parent_id, 
                   p5.parent_id, 
                   p6.parent_id) 
order       by 1, 2, 3, 4, 5, 6, 7;

См. этот скрипт

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

185
ответ дан trincot 21 August 2018 в 18:43
поделиться
  • 1
    Мне нравятся ваши объяснения. Он не просто дает ответ, он объясняет , почему он решает проблему, поэтому мы можем на самом деле учиться на ней. EDIT: тоже здорово, что он не полагается заранее знать количество уровней. – Byson 24 December 2015 в 11:57
  • 2
    @ Avión, это не то, что вам нужно положить где-то, это требование , что для всех записей это условие истинно. Если у вас есть одна или несколько записей, где parent_id > id, вы не можете использовать это решение. – trincot 13 March 2017 в 11:08
  • 3
    Да, это было бы возможно и проще. Если вы получите логику, используемую в этом ответе, это не должно быть трудно сделать. Задайте новый вопрос, если вы столкнулись с проблемой. – trincot 19 July 2017 в 14:59
  • 4
    Я пробовал основное решение на MySQL5.7 на своем компьютере, в моих собственных таблицах, но это не сработало из-за эквивалента предложения @pv: = concat (@pv, ',', id). Я исправил его, изменив его на длину (@pv: = concat (@pv, ',', id)) & gt; 0, так что это всегда так. – KC Wong 1 February 2018 в 10:54
  • 5
    @KCWong, здорово! Я вложил это улучшение в ответ. Благодаря! – trincot 1 February 2018 в 11:20
Другие вопросы по тегам:

Похожие вопросы: