如何检索自动引用的行递归

Question

我有下表：

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”或“父”，返回完整的进化系列。例如：

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

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

编辑:对此逻辑的解释：

口袋妖怪可以进化，口袋妖怪可以有进化，预进化和分裂进化：

分裂进化的例子：

​

正常进化的例子：

​

​

Answer 1

我猜你在MySQL v8上。如果不是的话，这是一个主要的问题，因为有可能运行递归CTE是一种具有分层数据的奢侈品。下面的一切都依赖于CTE。在没有MySQL 5的情况下，这仍然是可以做到的，但是我不会在这里详细说明，因为它没有未来。

首先，添加一个列来存储某一“进化链”的“起源”特征。为什么？因为您所面临的是一个典型的情况，即去正规化是一件非常好的事情。

如果不添加本列，则必须在整个地方执行过于复杂和性能不佳的查询，以检索特定pokemon链中的所有pokemon。

所以从

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值来定义所有的pokemons的最顶层父级：

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的UPDATE查询，以便确定地修复原始值：

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“，它必须检索ids: 1,2,3,4 给定"Raichu Alola“，它必须检索ids: 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

拉丘·阿洛拉

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

最后，在您的列(它是您的新朋友)上添加一个索引;)