Comment faire la requête SELECT récursive dans MySQL?

J’ai un tableau suivant:

col1 | col2 | col3 -----+------+------- 1 | a | 5 5 | d | 3 3 | k | 7 6 | o | 2 2 | 0 | 8 

Si un utilisateur cherche “1”, le programme examinera le col1 qui a “1”, puis il obtiendra une valeur dans col3 “5”, puis le programme continuera à rechercher “5” dans col1 et il obtiendra “3” dans col3 , et ainsi de suite. Donc, il va imprimer:

 1 | a | 5 5 | d | 3 3 | k | 7 

Si un utilisateur recherche “6”, il imprimera:

 6 | o | 2 2 | 0 | 8 

Comment créer une requête SELECT pour le faire?

modifier

La solution mentionnée par @leftclickben est également efficace. Nous pouvons également utiliser une procédure stockée pour le même.

 CREATE PROCEDURE get_tree(IN id int) BEGIN DECLARE child_id int; DECLARE prev_id int; SET prev_id = id; SET child_id=0; SELECT col3 into child_id FROM table1 WHERE col1=id ; create TEMPORARY table IF NOT EXISTS temp_table as (select * from table1 where 1=0); truncate table temp_table; WHILE child_id <> 0 DO insert into temp_table select * from table1 WHERE col1=prev_id; SET prev_id = child_id; SET child_id=0; SELECT col3 into child_id FROM TABLE1 WHERE col1=prev_id; END WHILE; select * from temp_table; END // 

Nous utilisons la table temporaire pour stocker les résultats de la sortie et comme les tables temporaires sont basées sur la session, nous ne verrons aucun problème concernant les données de sortie incorrectes.

SQL FIDDLE Demo

Essayez cette requête:

 SELECT col1, col2, @pv := col3 as 'col3' FROM table1 JOIN (SELECT @pv := 1) tmp WHERE col1 = @pv 

SQL FIDDLE Demo :

 | COL1 | COL2 | COL3 | +------+------+------+ | 1 | a | 5 | | 5 | d | 3 | | 3 | k | 7 | 

Remarque
parent_id valeur parent_id doit être inférieure à celle de child_id pour que cette solution fonctionne.

La réponse acceptée par @Meherzad ne fonctionne que si les données sont dans un ordre particulier. Il arrive de travailler avec les données de la question OP. Dans mon cas, je devais le modifier pour travailler avec mes données.

Remarque Cela ne fonctionne que lorsque “id” (col1 dans la question) de chaque enregistrement a une valeur GREATER THAN plus que “parent id” de cet enregistrement (col3 dans la question). C’est souvent le cas, car le parent devra normalement être créé en premier. Toutefois, si votre application autorise des modifications à la hiérarchie, où un élément peut être redéfini ailleurs, vous ne pouvez pas vous y fier.

Ceci est ma requête au cas où cela aiderait quelqu’un; Notez que cela ne fonctionne pas avec la question donnée car les données ne suivent pas la structure requirejse décrite ci-dessus.

 select t.col1, t.col2, @pv := t.col3 col3 from (select * from table1 order by col1 desc) t join (select @pv := 1) tmp where t.col1 = @pv 

La différence est que la commande table1 est ordonnée par col1 sorte que le parent le recherchera (la valeur de col1 du parent étant inférieure à celle de l’enfant).

La réponse de leftclickben a fonctionné pour moi, mais je voulais un chemin allant d’un nœud donné à l’arborescence à la racine, et ceux-ci semblaient aller dans l’autre sens. Donc, j’ai dû retourner certains champs et les renommer pour plus de clarté, et cela fonctionne pour moi, au cas où ce que quelqu’un d’autre veut aussi …

 item | parent ------------- 1 | null 2 | 1 3 | 1 4 | 2 5 | 4 6 | 3 

et

 select t.item_id as item_id, @pv:=t.parent as parent from (select * from item_tree order by item_id desc) t join (select @pv:=6)tmp where t.item_id=@pv; 

donne:

 item | parent ------------- 6 | 3 3 | 1 1 | null 

La procédure stockée est la meilleure façon de le faire. Parce que la solution de Meherzad ne fonctionnerait que si les données suivent le même ordre.

Si nous avons une structure de table comme celle-ci

 col1 | col2 | col3 -----+------+------ 3 | k | 7 5 | d | 3 1 | a | 5 6 | o | 2 2 | 0 | 8 

Ça ne marchera pas SQL Fiddle Demo

Voici un exemple de code de procédure pour obtenir la même chose.

 delimiter // CREATE PROCEDURE chainReaction ( in inputNo int ) BEGIN declare final_id int default NULL; SELECT col3 INTO final_id FROM table1 WHERE col1 = inputNo; IF( final_id is not null) THEN INSERT INTO results(SELECT col1, col2, col3 FROM table1 WHERE col1 = inputNo); CALL chainReaction(final_id); end if; END// delimiter ; call chainReaction(1); SELECT * FROM results; DROP TABLE if exists results; 

Si vous voulez pouvoir avoir un SELECT sans problèmes avec l’ID parent devant être inférieur à l’ID enfant, une fonction peut être utilisée. Il supporte également plusieurs enfants (comme un arbre devrait faire) et l’arbre peut avoir plusieurs têtes. Il garantit également de rompre si une boucle existe dans les données.

Je voulais utiliser le SQL dynamic pour pouvoir passer les noms de tables / colonnes, mais les fonctions de MySQL ne le supportent pas.

 DELIMITER $$ CREATE FUNCTION `isSubElement`(pParentId INT, pId INT) RETURNS int(11) DETERMINISTIC READS SQL DATA BEGIN DECLARE isChild,curId,curParent,lastParent int; SET isChild = 0; SET curId = pId; SET curParent = -1; SET lastParent = -2; WHILE lastParent <> curParent AND curParent <> 0 AND curId <> -1 AND curParent <> pId AND isChild = 0 DO SET lastParent = curParent; SELECT ParentId from `test` where id=curId limit 1 into curParent; IF curParent = pParentId THEN SET isChild = 1; END IF; SET curId = curParent; END WHILE; RETURN isChild; END$$ 

Ici, le test la table doit être modifié pour le vrai nom de la table et les colonnes (ParentId, Id) doivent être ajustées pour vos vrais noms.

Utilisation:

 SET @wantedSubTreeId = 3; SELECT * FROM test WHERE isSubElement(@wantedSubTreeId,id) = 1 OR ID = @wantedSubTreeId; 

Résultat :

 3 7 k 5 3 d 9 3 f 1 5 a 

SQL pour la création de tests:

 CREATE TABLE IF NOT EXISTS `test` ( `Id` int(11) NOT NULL, `ParentId` int(11) DEFAULT NULL, `Name` varchar(300) NOT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; insert into test (id, parentid, name) values(3,7,'k'); insert into test (id, parentid, name) values(5,3,'d'); insert into test (id, parentid, name) values(9,3,'f'); insert into test (id, parentid, name) values(1,5,'a'); insert into test (id, parentid, name) values(6,2,'o'); insert into test (id, parentid, name) values(2,8,'c'); 

EDIT: Voici un violon pour le tester vous-même. Cela m’a obligé à changer le délimiteur en utilisant celui qui était prédéfini, mais ça marche.