Можно ли сделать рекурсивный SQL-запрос в MySQL [duplicate]

Принимаемый ответ @Meherzad работает только в том случае, если данные находятся в определенном порядке. Это происходит с данными из вопроса ОП. В моем случае мне пришлось изменить его для работы с моими данными.

Примечание. Это работает только тогда, когда каждый идентификатор записи (col1 в вопросе) имеет значение БОЛЬШЕ, чем этот «родительский идентификатор» записи ( col3 в вопросе). Это часто бывает так, потому что обычно родитель должен быть создан первым. Однако, если ваше приложение допускает изменения в иерархии, где элемент может быть повторно зарегистрирован в другом месте, тогда вы не можете полагаться на это.

Это мой запрос, если он кому-то помогает; обратите внимание, что это не работает с данным вопросом, потому что данные не соответствуют требуемой структуре, описанной выше.

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

Разница в том, что table1 упорядочивается по col1, так что родитель будет после него (поскольку значение родительского col1 меньше, чем у ребенка).

Если вы хотите иметь SELECT без проблем с идентификатором родительского идентификатора, который должен быть ниже id ребенка, можно использовать функцию. Он поддерживает также несколько дочерних элементов (как дерево должно делать), и дерево может иметь несколько головок. Он также обеспечивает разрыв, если в данных существует цикл.

Я хотел использовать динамический SQL, чтобы иметь возможность передавать имена таблиц / столбцов, но функции в MySQL не поддерживают это.

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

Здесь таблица test должна быть изменена до имени реальной таблицы, а столбцы (ParentId, Id), возможно, придется скорректировать для ваших настоящих имен.

Использование:

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

Результат:

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

SQL для создания теста:

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: здесь скрипт проверить его самостоятельно. Это заставило меня изменить разделитель с помощью предопределенного, но он работает.

Сохраненная процедура - лучший способ сделать это. Поскольку решение Мехерзада будет работать только в том случае, если данные будут следовать одному и тому же порядку.

Если у нас есть такая структура таблицы

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

, она не будет работать. SQL Fiddle Demo

Вот пример кода процедуры для достижения того же.

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;