Форум пользователей MySQL

klow · 08.03.2019 12:33:43

Добрый день!
Есть таблица с полями ID и Parent_Id.
Нужно получить всех потомков для всех родителей и уровень вложенности (Level) начиная от самого верха Parent_Id = NULL
У меня получить решить это только двумя рекурсивными запросами (см. ниже). Возможно ли обойтись одним?

WITH RECURSIVE dn (id, node_id) AS 

( 

  SELECT id, id node_id FROM tabl d 

  UNION

  SELECT dn.ID, d.ID

  FROM dn JOIN tabl d ON dn.node_id = d.PARENT_ID 

),

 dn1 (id, Level) AS 

( 

  SELECT id, 0 level FROM tabl d WHERE d.PARENT_ID IS NULL

  UNION

  SELECT d.ID, dn1.level + 1 level 

  FROM dn1 JOIN tabl d ON dn1.id = d.PARENT_ID 

)

SELECT dn.id, dn.node_id, dn1.Level FROM dn JOIN dn1 ON dn.node_id = dn1.id;

vasya · 11.03.2019 02:11:38

т.е все возможные пары (потомок,родитель) + уровень потомка?
тогда 1 рекурсия для расчета уровня и декартово произведение за вычетом совпадений
что-то типа:

WITH RECURSIVE dn1 (id, Level) AS

(

  SELECT id, 0 level FROM tabl d WHERE d.PARENT_ID IS NULL

  UNION

  SELECT d.ID, dn1.level + 1 level

  FROM dn1 JOIN tabl d ON dn1.id = d.PARENT_ID

)

select t1.id, t2.id, t1.level from dn1 t1, dn1 t2 where t1.id <> t2.id;

klow · 11.03.2019 10:20:50

Спасибо, конечно, но просто декартово множество мне не нужно.
Нужно именно реальные связи родителя и потомков. Что этот родитель имеет таких потомков и их уровень.

vasya · 11.03.2019 13:59:27

ага, я уже сам утром понял, что глупость ночью написал

можно одной рекурсией получить (потомок, уровень, список родителей через запятую)
а потом декартово произведение и удаление лишних через find_in_set

sqlfiddle предоставляет только MySQL 5.6, поэтому пример для postgre - http://sqlfiddle.com/#!17/cb58e/14

WITH RECURSIVE dn1 (id, Level, path) AS 

( 

  SELECT id, 0 as level, cast(id as text) as path FROM category d WHERE d.PARENT_ID IS NULL

  UNION

  SELECT d.ID, dn1.level + 1 as level, concat(dn1.path,',',d.id) 

  FROM dn1 JOIN category d ON dn1.id = d.PARENT_ID 

)

SELECT t1.id, t2.id, t2.level, t1.path from dn1 as t1, dn1 as t2 

where cast(t1.id as text) = ANY(string_to_array(t2.path,','))

order by t2.id;

результат тот же, что и для вашего исходного запроса - http://sqlfiddle.com/#!17/cb58e/2

WITH RECURSIVE dn (id, node_id) AS ( 

  SELECT id, id node_id FROM category d 

  UNION

  SELECT dn.ID, d.ID

  FROM dn JOIN category d ON dn.node_id = d.PARENT_ID 

),

 dn1 (id, Level) AS 

( 

  SELECT id, 0 as level FROM category d WHERE d.PARENT_ID IS NULL

  UNION

  SELECT d.ID, dn1.level + 1 as level 

  FROM dn1 JOIN category d ON dn1.id = d.PARENT_ID 

)

SELECT dn.id, dn.node_id, dn1.Level FROM dn JOIN dn1 ON dn.node_id = dn1.id;

p.s. имхо, производительность вашего варианта будет лучше

klow · 11.03.2019 14:09:49

Спасибо, но лучше уж больше текста, но быстрее.
Приоритет, конечно, это скорость выполнения. К сожалению, даже в моем варианте это далеко не идеально - не используются индексы, при использовании этого запроса.

ЗЫ
string_to_array - думаю, будет проблематично использовать для MySql.

vasya · 11.03.2019 14:49:50

klow написал:
string_to_array - думаю, будет проблематично использовать для MySql.

зато в MySQL есть find_in_set

klow написал:
К сожалению, даже в моем варианте это далеко не идеально - не используются индексы, при использовании этого запроса.

емнип, к cte применяются те же оптимизации, что и для производных таблиц - индексация создаваемой временной таблицы (и даже может построить несколько разных индексов, если cte упоминается в запросе неоднократно)
возможно у вас маленькая выборка и оптимизатор решает сделать перебор, чем строить индекс

Форум пользователей MySQL

#1 08.03.2019 12:33:43

RECURSIVE и Level

#2 11.03.2019 02:11:38

Re: RECURSIVE и Level

#3 11.03.2019 10:20:50

Re: RECURSIVE и Level

#4 11.03.2019 13:59:27

Re: RECURSIVE и Level

#5 11.03.2019 14:09:49

Re: RECURSIVE и Level

#6 11.03.2019 14:49:50

Re: RECURSIVE и Level

klow написал:

klow написал:

Board footer