Jak wykonać rekursywne zapytanie SELECT w MySQL?

Mam następującą tabelę:

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

Jeśli użytkownik wyszukuje „1”, program sprawdzi, col1który ma „1”, a następnie uzyska wartość col3„5”, a następnie program będzie kontynuował wyszukiwanie „5” w col1i otrzyma „3” w col3i tak dalej. Więc wydrukuje:

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

Jeśli użytkownik wyszuka „6”, wydrukuje:

6   | o   | 2
2   | 0   | 8

Jak zbudować SELECTzapytanie, aby to zrobić?

Edytować

Rozwiązanie wspomniane przez @leftclickben jest również skuteczne. Możemy również użyć procedury składowanej do tego samego.

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

Używamy tabeli tymczasowej do przechowywania wyników danych wyjściowych, a ponieważ tabele tymczasowe są oparte na sesjach, nie będzie żadnego problemu dotyczącego nieprawidłowych danych wyjściowych.

SQL FIDDLE Demo

Spróbuj tego zapytania:

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

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

Uwaga
parent_id wartość powinna być mniejsza niż child_iddla tego rozwiązania do pracy.

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

Uwaga Działa to tylko wtedy, gdy „id” każdego rekordu (kol1 w pytaniu) ma wartość WIĘKSZĄ NIŻ „identyfikator rodzica” tego rekordu (kolumna3 w pytaniu). Dzieje się tak często, ponieważ zwykle rodzic musi zostać utworzony jako pierwszy. Jeśli jednak Twoja aplikacja zezwala na zmiany w hierarchii, w których element może zostać przeniesiony w inne miejsce, nie możesz na tym polegać.

To jest moje zapytanie na wypadek, gdyby komuś pomogło; zauważ, że nie działa z danym pytaniem, ponieważ dane nie mają wymaganej struktury opisanej 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 table1jest to porządkowane według, col1aby rodzic był po nim (ponieważ wartość rodzica col1jest niższa niż wartość dziecka).

Odpowiedź leftclickben działała dla mnie, ale chciałem utworzyć ścieżkę z danego węzła w górę drzewa do korzenia, a te wydawały się prowadzić w drugą stronę, w dół drzewa. Musiałem więc odwrócić niektóre pola i zmienić ich nazwy dla większej przejrzystości, a to działa w moim przypadku, na wypadek, gdyby ktoś inny chciał tego też -

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

i

select t.item_id as item, @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 to najlepszy sposób na zrobienie tego. Ponieważ rozwiązanie Meherzad działałoby tylko wtedy, gdy dane są 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

To nie zadziała. SQL Fiddle Demo

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, gdy identyfikator nadrzędny musi być niższy niż identyfikator dziecka, możesz użyć funkcji. Obsługuje również wiele dzieci (tak jak powinno to robić drzewo), a 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 przekazywać 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 testmusi zostać zmodyfikowana do rzeczywistej nazwy tabeli, a kolumny (ParentId, Id) mogą wymagać dostosowania do twoich prawdziwych nazw.

Stosowanie :

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

EDYCJA: Oto skrzypce do samodzielnego przetestowania. Zmusiło mnie to do zmiany ogranicznika za pomocą predefiniowanego, ale działa.

Opierając się na Master DJon

Oto uproszczona funkcja, która zapewnia dodatkowe narzędzie zwracania głębokości (w przypadku, gdy chcesz użyć logiki do uwzględnienia zadania nadrzędnego lub wyszukiwania na określonej głębokości)

DELIMITER $$
FUNCTION `childDepth`(pParentId INT, pId INT) RETURNS int(11)
    READS SQL DATA
    DETERMINISTIC
BEGIN
DECLARE depth,curId int;
SET depth = 0;
SET curId = pId;

WHILE curId IS not null AND curId <> pParentId DO
    SELECT ParentId from test where id=curId limit 1 into curId;
    SET depth = depth + 1;
END WHILE;

IF curId IS NULL THEN
    set depth = -1;
END IF;

RETURN depth;
END$$

Stosowanie:

select * from test where childDepth(1, id) <> -1;