Как рекурсивно извлекать строки с автоматическими ссылками

ProgramBox

Как рекурсивно извлекать строки с автоматическими ссылками

Post author:admin
Запись опубликована:28 марта, 2023
Post category:Вопросы по программированию

#mysql #sql #group-by #where-clause

#mysql — сервер #sql #группировать по #where-предложение #mysql

Вопрос:

У меня есть следующая таблица:

 CREATE TABLE poke_evolutions (
    id integer PRIMARY KEY,
    poke_id VARCHAR(20),
    parent VARCHAR(20,
    method VARCHAR(20)
)

И этот образец данных:

 INSERT INTO poke_evolutions (id,poke_id,parent,method)
     VALUES (1,'Pichu',null,'Happiness')
INSERT INTO poke_evolutions (id,poke_id,parent,method)
     VALUES (2,'Pikachu','Pichu','Thunderstone')
INSERT INTO poke_evolutions (id,poke_id,parent,method)
    VALUES (3,'Raichu','Pikachu','Thunderstone')
INSERT INTO poke_evolutions (id,poke_id,parent,method)
    VALUES (4,'Raichu Alola','Pikachu','Thunderstone')

Мне нужен запрос, который, учитывая ‘poke_id’ или ‘parent’, возвращает полное семейство эволюций. Например:

 Given "Pikachu" it must retrieve ids: 1,2,3,4

Given "Raichu Alola" it must retrieve ids: 1,2,3,4

РЕДАКТИРОВАТЬ: объяснение логики этого:

Покемон может эволюционировать , у покемона могут быть эволюции, предэволюции и расщепленные эволюции :

Пример расщепленных эволюций:

Пример нормальной эволюции:

1. Пожалуйста, объясните логику. Это не очевидно, если вы не фанат покемонов.

2. Выберите ОДНУ СУБД, затем удалите несвязанный тег.

3. Дизайн данных от вас или вы перекачали его в другое место? Потому что, честно говоря, мне это кажется плохим. Это неправильный способ хранения иерархических данных и управления ими. Это может работать со сложными запросами, но в какой-то момент у вас возникнет много проблем. Например, у вас должна быть таблица символов со столбцами ID number, Name (Pichu, Pikachu, Raichu), Instance NULL, 1, 2 (для ваших 2 экземпляров Raichu). И затем в вашей таблице evo столбцами должны быть ID, character_id, parent_char_id, description_of_evo. Слишком длинный комментарий, я надеюсь, вы его поняли.

4. Я говорю вам, что символы и эволюции — это нечто другое, и они должны быть разделены на 2 таблицы. Один из них должен обрабатывать иерархию с 2 столбцами «Character_ID» и «Parent_Character_id», он может работать в обеих таблицах. Самый первый родитель (Пичу, Бульбасавр) в качестве parent_id=NULL. Это единственный эффективный способ управлять данными о найме и запрашивать их в SQL. Но ваша главная проблема прямо сейчас заключается в том, что ваша таблица evo хранит символ в 3 столбцах: poke_id, evo, split_evo. Это явно неправильно и рано или поздно приведет вас к множеству проблем. Исправьте это в первую очередь

5. Если кто-то придет сюда с запросом, который решит вашу проблему, это только замаскирует реальность: ваше моделирование данных неверно. И позже возникнет множество проблем. Я мог бы, но я не дам вам ответа, потому что это НЕ делает вам одолжения. Напротив, если вы измените свою структуру данных и то, что я сказал, вы легко найдете массу примеров иерархических запросов, которые помогут вам достичь того, чего вы хотите, и за его пределами.

Ответ №1:

Я предполагаю , что вы используете MySQL версии 8 . Если нет, то это серьезная проблема, потому что наличие возможности запускать рекурсивный CTE — роскошь с иерархическими данными. Все, что приведено ниже, зависит от CTE. Это все еще возможно сделать без CTE, в MySQL 5, но я не буду подробно описывать это здесь, потому что у этого нет будущего.

Прежде всего, добавьте столбец, в котором будет храниться символ «происхождения» определенной «цепочки» эволюции. Почему ? Потому что вы сталкиваетесь с типичной ситуацией, когда денормализация — это очень хорошая вещь.

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

Итак, начните с

 ALTER table poke_evolutions ADD origin (VARCHAR(20)) default NULL;

И тогда ваш стол становится

 id  poke_id         parent         method         origin
1   Pichu                          Happiness    
2   Pikachu         Pichu          Thunderstone     
3   Raichu          Pikachu        Thunderstone     
4   Raichu Alola    Pikachu        Thunderstone     
5   Bulbasaur                      Happiness    
6   Ivysaur         Bulbasaur      Anything     
7   Venusaur        Ivysaur        Anything

Но что замечательно, так это то, что вы можете определить один раз для всех origin значение, используя рекурсивный CTE, который ищет самого верхнего родителя всех ваших покемонов :

 WITH recursive cte AS(
      SELECT *, poke_id AS topparent 
      FROM poke_evolutions 
      WHERE parent IS NULL
  UNION ALL
      SELECT p.*, c.topparent 
      FROM poke_evolutions p JOIN cte c ON c.poke_id = p.parent
      WHERE p.poke_id <> p.parent
)

select * from cte


id  poke_id         parent         method           origin  topparent
1   Pichu           Happiness                               Pichu
5   Bulbasaur       Happiness                               Bulbasaur
2   Pikachu         Pichu          Thunderstone             Pichu
6   Ivysaur         Bulbasaur      Anything                 Bulbasaur
3   Raichu          Pikachu        Thunderstone             Pichu
4   Raichu Alola    Pikachu        Thunderstone             Pichu
7   Venusaur    Ivysaur            Anything                 Bulbasaur

Поэтому просто запустите запрос НА ОБНОВЛЕНИЕ, который использует ваш CTE, чтобы окончательно исправить исходное значение :

 WITH recursive cte AS(
      SELECT *, poke_id AS topparent 
      FROM poke_evolutions 
      WHERE parent IS NULL
  UNION ALL
      SELECT p.*, c.topparent 
      FROM poke_evolutions p JOIN cte c ON c.poke_id = p.parent
      WHERE p.poke_id <> p.parent
)
UPDATE poke_evolutions p, cte c
SET p.origin= c.topparent
WHERE p.poke_id=c.poke_id;

Ваша таблица становится :

 SELECT * from  poke_evolutions;

id  poke_id         parent         method         origin
1   Pichu                          Happiness      Pichu
2   Pikachu         Pichu          Thunderstone   Pichu 
3   Raichu          Pikachu        Thunderstone   Pichu  
4   Raichu Alola    Pikachu        Thunderstone   Pichu  
5   Bulbasaur                      Happiness      Bulbasaur
6   Ivysaur         Bulbasaur      Anything       Bulbasaur
7   Venusaur        Ivysaur        Anything       Bulbasaur

И теперь вы можете легко делать то, что хотели в первую очередь

Учитывая «Pikachu», он должен получить идентификаторы: 1,2,3,4

Учитывая «Raichu Alola», он должен получить идентификаторы: 1,2,3,4

С Пичу

 select poke_id from poke_evolutions 
where origin = (select origin from poke_evolutions WHERE poke_id='Pichu');

poke_id
Pichu
Pikachu
Raichu
Raichu Alola

С Raichu Alola

 select poke_id from poke_evolutions 
where origin = (select origin from poke_evolutions WHERE poke_id='Raichu Alola');

poke_id
Pichu
Pikachu
Raichu
Raichu Alola

С Ивизавром

 select poke_id from poke_evolutions 
where origin = (select origin from poke_evolutions WHERE poke_id='Ivysaur');

poke_id
Bulbasaur
Ivysaur
Venusaur

И, наконец, добавьте индекс в свой origin столбец, это ваш новый друг 😉

1. Ваш ответ — чистое золото , спасибо за сегодняшний урок по оптимизации базы данных !

2. @NexussimLements добро пожаловать. Поэтому я предполагаю, что это было для MySQL, а не для sqlite. Если это так, отредактируйте свой вопрос и пометьте его должным образом. Мастер Гордон удалил тег hte MySQL