Jak wykonać rekurencyjne zapytanie SELECT w MySQL?

Zaakceptowana odpowiedź przez @ Meherzad działa tylko wtedy, gdy dane są w określonej kolejności. Tak się składa, że działa z danymi z pytania OP. W moim przypadku musiałem zmodyfikować go, aby działał z moimi danymi.

Uwaga działa to tylko wtedy, gdy " id " każdego rekordu (col1 w pytaniu) ma wartość większą niż "ID rodzica" tego rekordu (col3 w pytaniu). Często tak jest, ponieważ zwykle rodzic musi być utworzony jako pierwszy. Jeśli jednak Twoja aplikacja pozwala na zmiany w hierarchia, gdzie element może być ponownie rodzic gdzie indziej, wtedy nie można na tym polegać.

To jest moje zapytanie na wypadek, gdyby komuś pomogło; zauważ, że nie działa z danym pytaniem, ponieważ dane nie są zgodne z wymaganą strukturą opisaną powyżej.

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

Różnica polega na tym, że table1 jest uporządkowana przez col1 tak, że rodzic będzie po nim (ponieważ wartość rodzica col1 jest niższa od wartości dziecka).

Odpowiedź Leftclickben zadziałała dla mnie, ale chciałem ścieżkę z danego węzła z powrotem w górę drzewa do korzenia, a te zdawały się iść w drugą stronę, w dół drzewa. Więc, musiałem odwrócić niektóre pola i zmienić nazwę dla jasności, a to działa dla mnie, na wypadek, gdyby ktoś jeszcze tego chciał--{]}

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

I

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;

Daje:

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

Procedura składowana jest najlepszym sposobem, aby to zrobić. Ponieważ rozwiązanie Meherzada będzie działać tylko wtedy, gdy dane będą zgodne z tą samą kolejnością.

Jeśli mamy taką strukturę tabeli

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

Oto przykładowy kod procedury, aby osiągnąć to samo.

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;

Jeśli chcesz mieć SELECT bez problemów ID rodzica musi być niższy niż ID dziecka, można użyć funkcji. Obsługuje również wiele dzieci (jak powinno to zrobić drzewo) i drzewo może mieć wiele głów. Zapewnia również przerwanie, jeśli w danych istnieje pętla.

Chciałem użyć dynamicznego SQL, aby móc przekazać nazwy tabel/kolumn, ale funkcje w MySQL nie obsługują tego.

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$$

Tutaj Tabela test musi zostać zmieniona na prawdziwą nazwę tabeli kolumny (ParentId,Id) mogą wymagać dostosowania do prawdziwych nazw.

Użycie:

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

Wynik:

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

SQL do tworzenia testów:

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: oto fiddle , aby przetestować go samodzielnie. Zmusiło mnie to do zmiany ogranicznika przy użyciu predefiniowanego, ale to działa.