SQLinfo.ru - Все о MySQL

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

Задавайте вопросы, мы ответим

Вы не зашли.

#1 08.03.2019 12:33:43

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 373

RECURSIVE и Level

Добрый день!
Есть таблица с полями 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;

Неактивен

 

#2 11.03.2019 02:11:38

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5603

Re: RECURSIVE и Level

т.е все возможные пары (потомок,родитель) + уровень потомка?
тогда 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;

Неактивен

 

#3 11.03.2019 10:20:50

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 373

Re: RECURSIVE и Level

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

Неактивен

 

#4 11.03.2019 13:59:27

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5603

Re: RECURSIVE и Level

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

можно одной рекурсией получить (потомок, уровень, список родителей через запятую)
а потом декартово произведение и удаление лишних через 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. имхо, производительность вашего варианта будет лучше

Неактивен

 

#5 11.03.2019 14:09:49

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 373

Re: RECURSIVE и Level

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

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

Неактивен

 

#6 11.03.2019 14:49:50

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5603

Re: RECURSIVE и Level

klow написал:

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

зато в MySQL есть find_in_set

klow написал:

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

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

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson