Comment faire la recherche SELECT récursive dans MySQL?

la réponse acceptée par @Meherzad ne fonctionne que si les données sont dans un ordre particulier. Il se trouve que cela fonctionne avec les données de la question de L'OP. Dans mon cas, j'ai eu à le modifier pour travailler avec mes données.

Note cela ne fonctionne que lorsque la valeur "id" (col1 dans la question) de chaque enregistrement est supérieure à la valeur "parent id" (col3 dans la question). C'est souvent le cas, car normalement le parent devra être créé en premier. Toutefois, si votre application permet des changements à la hiérarchie, où un élément peut être re-parenté ailleurs, alors vous ne pouvez pas compter sur cela.

c'est ma requête dans le cas où il aide quelqu'un; notez qu'il ne fonctionne pas avec la question donnée parce que les données ne suivent pas la structure requise 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 table1 est commandé par col1 , de sorte que le parent sera après (car les parents La valeur col1 est inférieure à celle de l'enfant.

leftclickben réponse a fonctionné pour moi, mais je voulais un chemin à partir d'un nœud donné sauvegarder l'arbre à la racine, et ces semblait aller dans l'autre sens, vers le bas de l'arbre. Donc, j'ai dû changer certains champs et renommé pour plus de clarté, et cela fonctionne pour moi, au cas où c'est 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

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 suivaient 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 le même.

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 être en mesure d'avoir un SELECT sans problèmes de l'id parent devant être inférieur à l'id enfant, une fonction pourrait être utilisée. Il supporte aussi plusieurs enfants (comme un arbre devrait le faire) et l'arbre peut avoir plusieurs têtes. Il assure aussi de rompre si une boucle existe dans les données.

je voulais utiliser le SQL dynamique pour pouvoir passer les noms de table/colonnes, mais les fonctions dans MySQL ne supportent pas cela.

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, la table test doit être modifié au nom réel de la table et les colonnes (ParentId,Id) peuvent devoir être ajustées pour vos noms réels.

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 à tester vous-même. Ça m'a forcé à changer le délimiteur en utilisant le prédéfini, mais ça marche.