Mam następującą tabelę:

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

Te przykładowe dane:

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')

Potrzebuję zapytania, że biorąc pod uwagę "poke_id" lub "rodzic", zwraca pełną rodzinę ewolucji. Na przykład:

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

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

Edytuj: Wyjaśnienie logiki tego:

Pokemon może ewoluować, pokemon może mieć ewolucje, ewolucje i podzielone ewolucje:

Przykład podziemnych ewolucji: Wpisz opis obrazu tutaj

Przykład normalnej ewolucji:

enter image description here

0
Nexussim Lements 7 wrzesień 2020, 10:51

1 odpowiedź

Najlepsza odpowiedź

Zakładam, że jesteś na MySQL V8. Jeśli nie jest to poważna kwestia, ponieważ mając możliwość prowadzenia rekurencyjnego CTE, jest luksusem z hierarchicznymi danymi. Wszystko, co znajduje się na CTE. Nadal jest to możliwe, aby zrobić to bez CTE, na MySQL 5, ale nie wyszedłem go tutaj tutaj, ponieważ nie ma przyszłości.

Przede wszystkim dodaj kolumnę, która zapisuje znak "pochodzenia" pewnego "łańcucha" ewolucji. Dlaczego ? Ponieważ stoisz w obliczu typowej sytuacji, w której Denormalizacja jest bardzo dobrą rzeczą.

Jeśli nie dodasz tej kolumny, będziesz musiał wykonać ocenę i niepotrzebne zapytania w całym miejscu do pobierania wszystkich pokemonów w łańcuchu konkretnego pokemona.

Więc zacznij od

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

A potem twoja tabela staje się

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     

Ale co jest świetne, jest to, że możesz zdefiniować raz na wartość origin, przy użyciu rekurencyjnego CTE, który przeszukał najwyższego rodzica wszystkich pokemonów:

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

Wystarczy uruchomić zapytanie aktualizacji, które używa twojego CTE, aby ostatecznie naprawić wartość pochodzenia:

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;

Twoja tabela staje się:

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

A teraz możesz łatwo robić to, czego chciałeś w pierwszej kolejności

Biorąc pod uwagę "Pikachu", musi odzyskać IDS: 1,2,3,4

Biorąc pod uwagę "Raichu Alola", musi odzyskać IDS: 1,2,3,4

Z Pichu.

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

poke_id
Pichu
Pikachu
Raichu
Raichu Alola

Z 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

Z Ivysaur.

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

poke_id
Bulbasaur
Ivysaur
Venusaur

I na koniec dodaj indeks na kolumnie origin, jego nowego przyjaciela;)

2
Thomas G 7 wrzesień 2020, 15:42