Pregunta ¿Cómo hacer la consulta SELECCIONAR recursiva en MySQL?


Tengo una siguiente mesa:

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 verá el col1 que tiene "1" entonces obtendrá un valor en col3 "5", luego el programa continuará buscando "5" en col1 y obtendrá "3" en col3, y así. Entonces se imprimirá:

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

Si un usuario busca "6", imprimirá:

6   | o   | 2
2   | 0   | 8

Cómo construir un SELECT pregunta para hacer eso?


75
2018-05-13 00:31


origen


Respuestas:


Editar

La solución mencionada por @leftclickben también es efectiva. También podemos usar un procedimiento almacenado para el 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 la tabla temporal para almacenar los resultados de la salida y como las tablas temporales se basan en sesiones, no existirá ningún problema con respecto a que los datos de salida sean incorrectos.

SQL FIDDLE Demo

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


66
2018-05-13 03:21



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

Nota Esto solo funciona cuando el "ID" de cada registro (col1 en la pregunta) tiene un valor MAYOR QUE el "ID padre" de ese registro (col3 en la pregunta). Este es a menudo el caso, porque normalmente el padre deberá crearse primero. Sin embargo, si su aplicación permite cambios en la jerarquía, donde un elemento puede volver a parentarse en otro lugar, entonces no puede confiar en esto.

Esta es mi consulta en caso de que ayude 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 table1 está siendo ordenado por col1 para que el padre lo busque (dado que el padre col1 el valor es menor que el del niño).


49
2017-07-23 04:30



La respuesta de Leftclickben funcionó para mí, pero quería un camino desde un nodo dado, una copia de seguridad del árbol hasta la raíz, y estos parecían ir hacia el otro lado, hacia abajo del árbol. Entonces, tuve que darle la vuelta a algunos de los campos y cambiar el nombre por claridad, y esto funciona para mí, en caso de que esto sea lo que los demás también quieren--

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

y

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;

da:

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

15
2017-09-25 05:16



El procedimiento almacenado es la mejor manera de hacerlo. Porque la solución de Meherzad funcionaría solo 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;

7
2018-01-27 10:37



Si desea poder tener un SELECT sin problemas de que el id principal deba ser menor que el id del hijo, se podría usar una función. También admite varios hijos (como debería hacer un árbol) y el árbol puede tener varias cabezas. También asegura romper si existe un bucle en los datos.

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

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 mesa test tiene que ser modificado para el nombre de la tabla real y las columnas (ParentId, Id) pueden tener que ajustarse para sus nombres reales.

Uso:

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

Resultado:

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

SQL para la creación de prueba:

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.


6
2018-06-23 13:23