Как сделать рекурсивный запрос SELECT в MySQL?

я получил следующую таблицу:



col1 | col2 | col3

-----+------+-------

1    | a    | 5

5    | d    | 3

3    | k    | 7

6    | o    | 2

2    | 0    | 8


если пользователь ищет "1", программа будет смотреть на col1 что имеет "1", то он получит значение в col3 "5", после чего программа продолжит поиск" 5 " в col1 и он получит "3" в col3 и так далее. Так что он будет распечатывать:



1   | a   | 5

5   | d   | 3

3   | k   | 7


если пользователь ищет "6", он распечатает:



6   | o   | 2

2   | 0   | 8


как построить SELECT запрос для этого?

765   5  

5 ответов:

Edit

решение, упомянутое @leftclickben, также эффективно. Мы также можем использовать хранимую процедуру для того же.

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

мы используем временную таблицу для хранения результатов вывода, и поскольку временные таблицы основаны на сеансе, мы не будем иметь никаких проблем с неверными выходными данными.

SQL FIDDLE Demo

Попробуйте этот запрос: 

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 |

Примечание
parent_id значение должно быть меньше, чем child_id чтобы это решение работало.

66  
2015-11-30 19:33:18

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

Примечание это работает только тогда, когда "id" каждой записи (col1 в вопросе) имеет значение больше, чем "родительский id" этой записи (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 стоимость ниже, чем у ребенка).

49  
2014-07-23 07:30:48

ответ leftclickben работал для меня, но я хотел путь от данного узла обратно вверх по дереву к корню, и они, казалось, шли в другую сторону, вниз по дереву. Итак, мне пришлось перевернуть некоторые поля и переименовать для ясности, и это работает для меня, если это то, что хочет кто-то еще -

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

и

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;

выдает:

item | parent
-------------
6    | 3
3    | 1
1    | null
15  
2015-09-25 08:16:12

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

Если у нас есть структура таблицы, как это

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;
7  
2015-05-31 14:17:03

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

Я хотел использовать динамический 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: вот это скрипка чтобы проверить это самостоятельно. Это заставило меня изменить разделитель, используя предопределенный, но он работает.

6  
2016-07-04 04:33:43

Comments

    Ничего не найдено.
Click here to cancel reply.