Frage Wie macht man die rekursive SELECT-Abfrage in MySQL?


Ich habe eine folgende Tabelle:

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

Wenn ein Benutzer nach "1" sucht, schaut das Programm auf die col1 das hat "1" dann wird es einen Wert in bekommen col3 "5", dann sucht das Programm weiterhin nach "5" in col1 und es wird "3" drin sein col3, und so weiter. So wird es ausdrucken:

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

Wenn ein Benutzer nach "6" sucht, wird Folgendes ausgedruckt:

6   | o   | 2
2   | 0   | 8

Wie baue ich ein? SELECT Frage, um das zu tun?


75
2018-05-13 00:31


Ursprung


Antworten:


Bearbeiten

Die von @leftclickben erwähnte Lösung ist ebenfalls effektiv. Wir können auch eine gespeicherte Prozedur für dasselbe verwenden.

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

Wir verwenden die temporäre Tabelle, um Ergebnisse der Ausgabe zu speichern, und da die temporären Tabellen sitzungsbasiert sind, wird es kein Problem bezüglich der Ausgabedaten geben, die falsch sind.

SQL FIDDLE Demo

Versuchen Sie diese Abfrage:

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 |


66
2018-05-13 03:21



Die akzeptierte Antwort von @Meherzad funktioniert nur, wenn die Daten in einer bestimmten Reihenfolge sind. Es funktioniert zufällig mit den Daten aus der OP-Frage. In meinem Fall musste ich es ändern, um mit meinen Daten zu arbeiten.

Hinweis Dies funktioniert nur, wenn die "ID" jedes Datensatzes (Spalte 1 in der Frage) einen Wert hat, der größer ist als die "Eltern-ID" dieses Datensatzes (Spalte 3 in der Frage). Dies ist häufig der Fall, da normalerweise zuerst das übergeordnete Element erstellt werden muss. Wenn Ihre Anwendung jedoch Änderungen an der Hierarchie zulässt, bei der ein Element an anderer Stelle neu definiert werden kann, können Sie sich nicht darauf verlassen.

Dies ist meine Abfrage, falls es jemandem hilft; Beachten Sie, dass es bei der angegebenen Frage nicht funktioniert, da die Daten nicht der oben beschriebenen erforderlichen Struktur entsprechen.

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

Der Unterschied ist der table1 wird bestellt von col1 so dass der Elternteil danach sein wird (seit dem Elternteil col1 Wert ist niedriger als der des Kindes).


49
2017-07-23 04:30



leftclickbens Antwort funktionierte für mich, aber ich wollte einen Pfad von einem bestimmten Knoten zurück zum Baum zur Wurzel, und diese schienen in die andere Richtung zu gehen, den Baum hinunter. Also musste ich einige der Felder umdrehen und für Klarheit umbenennen, und das funktioniert für mich, falls das jemand anderes will--

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

und

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;

gibt:

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

15
2017-09-25 05:16



Gespeicherte Prozedur ist der beste Weg, es zu tun. Denn Meherzads Lösung würde nur funktionieren, wenn die Daten der gleichen Reihenfolge folgen.

Wenn wir eine solche Tabellenstruktur haben

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

Es wird nicht funktionieren. SQL Fiddle Demo

Hier ist ein Beispielcode, um das gleiche zu erreichen.

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;

7
2018-01-27 10:37



Wenn Sie in der Lage sein möchten, ein SELECT ohne Probleme zu haben, dass die Eltern-ID niedriger als die Kind-ID sein muss, könnte eine Funktion verwendet werden. Es unterstützt auch mehrere Kinder (wie ein Baum tun sollte) und der Baum kann mehrere Köpfe haben. Es stellt auch sicher, dass es bricht, wenn eine Schleife in den Daten existiert.

Ich wollte dynamisches SQL verwenden, um die Tabellen- / Spaltennamen zu übergeben, aber Funktionen in MySQL unterstützen dies nicht.

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

Hier, der Tisch test muss zu dem echten Tabellennamen geändert werden und die Spalten (ParentId, Id) müssen möglicherweise für Ihre echten Namen angepasst werden.

Verwendung :

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

Ergebnis:

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

SQL für die Test-Erstellung:

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: Hier ist ein Geige um es selbst zu testen. Es hat mich gezwungen, das Trennzeichen mit dem vordefinierten zu ändern, aber es funktioniert.


6
2018-06-23 13:23