¿Cómo hacer la consulta SELECT recursiva en MySQL?

Obtuve la siguiente tabla:

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

Si un usuario busca "1", el programa mirará el col1que tiene "1", luego obtendrá un valor en col3"5", luego el programa continuará buscando "5" col1y obtendrá "3". en col3, y así sucesivamente. Entonces imprimirá:

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

Si un usuario busca "6", imprimirá:

6   | o   | 2
2   | 0   | 8

¿Cómo crear una SELECTconsulta para hacer eso?

May 13 '13 07:05 Tum

Editar

La solución mencionada por @leftclickben también es efectiva. También podemos utilizar un procedimiento almacenado para lo mismo.

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

Estamos utilizando una tabla temporal para almacenar los resultados de la salida y, como las tablas temporales se basan en sesiones, no habrá ningún problema relacionado con que los datos de salida sean incorrectos.

SQL FIDDLE Demo

~~Pruebe esta consulta:~~

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 |

El valor de nota
parent_id debe ser menor que child_idpara que esta solución funcione.

May 13 '2013 03:05 Meherzad

La respuesta aceptada por @Meherzad solo funciona si los datos están en un orden particular. Funciona con los datos de la pregunta OP. En mi caso tuve que modificarlo para que funcionara con mis datos.

Nota Esto solo funciona cuando la "id" de cada registro (col1 en la pregunta) tiene un valor MAYOR QUE la "id principal" de ese registro (col3 en la pregunta). Este suele ser el caso, porque normalmente será necesario crear primero el padre. Sin embargo, si su aplicación permite cambios en la jerarquía, donde un elemento puede volver a estar relacionado en otro lugar, entonces no puede confiar en esto.

Esta es mi consulta por si le sirve a alguien; tenga en cuenta que no funciona con la pregunta dada porque los datos no siguen la estructura requerida descrita anteriormente.

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 diferencia es que table1se ordena para col1que el padre esté detrás (ya que el col1valor del padre es menor que el del niño).

Jul 23 '2014 04:07 leftclickben

La respuesta de leftclickben funcionó para mí, pero quería una ruta desde un nodo determinado de regreso al árbol hasta la raíz, y parecía que iban en la otra dirección, hacia abajo del árbol. Entonces, tuve que cambiar algunos de los campos y cambiarles el nombre para mayor claridad, y esto funciona para mí, en caso de que esto sea lo que alguien más quiera también...

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

y

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;

da:

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

Sep 25 '2015 05:09 BoB3K

El procedimiento almacenado es la mejor manera de hacerlo. Porque la solución de Meherzad sólo funcionaría si los datos siguen el mismo orden.

Si tenemos una estructura de tabla como esta

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

No funcionará.SQL Fiddle Demo

Aquí hay un código de procedimiento de muestra para lograr lo mismo.

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;

Jan 27 '2014 10:01 Jazmin

Si desea poder tener un SELECT sin problemas de que la identificación del padre tenga que ser menor que la identificación del hijo, se podría usar una función. También admite varios hijos (como debería hacerlo un árbol) y el árbol puede tener varias cabezas. También garantiza la interrupción si existe un bucle en los datos.

Quería usar SQL dinámico para poder pasar los nombres de tablas/columnas, pero las funciones en MySQL no lo admiten.

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

Aquí, la tabla testdebe modificarse al nombre real de la tabla y es posible que las columnas (ParentId,Id) deban ajustarse a sus nombres reales.

Uso:

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

Resultado :

SQL para la creación de pruebas:

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

EDITAR: Aquí hay un violín para probarlo usted mismo. Me obligó a cambiar el delimitador usando el predefinido, pero funciona.

Jun 23 '2016 13:06 Master DJon

¿Cómo hacer la consulta SELECT recursiva en MySQL?

SQL FIDDLE Demo:

`SQL FIDDLE Demo`: