如何在 MySQL 中进行递归 SELECT 查询？

Question

提问by Tum

I got a following table:

我得到了下表：

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

If a user searches for "1", the program will look at the col1that has "1" then it will get a value in col3"5", then the program will continue to search for "5" in col1and it will get "3" in col3, and so on. So it will print out:

如果用户搜索“1”，程序会查看col1有“1”的那个，然后它会得到一个col3“5”的值，然后程序会继续搜索“5” col1，它会得到“3”在col3，等等。所以它会打印出：

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

If a user search for "6", it will print out:

如果用户搜索“6”，它会打印出：

6   | o   | 2
2   | 0   | 8

How to build a SELECTquery to do that?

如何构建SELECT查询来做到这一点？

Answer 1

采纳答案by Meherzad

Edit

编辑

Solution mentioned by @leftclickben is also effective. We can also use a stored procedure for the same.

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

We are using temp table to store results of the output and as the temp tables are session based we wont there will be not be any issue regarding output data being incorrect.

我们使用临时表来存储输出结果，并且由于临时表是基于会话的，因此我们不会有任何关于输出数据不正确的问题。

SQL FIDDLE Demo

~~Try this query:~~

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

Note
parent_idvalue should be less than the child_idfor this solution to work.

注意
parent_id值应小child_id于此解决方案的工作。

Answer 2

回答by leftclickben

The accepted answer by @Meherzad only works if the data is in a particular order. It happens to work with the data from the OP question. In my case, I had to modify it to work with my data.

@Meherzad 接受的答案仅适用于数据按特定顺序排列的情况。它恰好适用于来自 OP 问题的数据。就我而言，我必须修改它才能处理我的数据。

NoteThis only works when every record's "id" (col1 in the question) has a value GREATER THAN that record's "parent id" (col3 in the question). This is often the case, because normally the parent will need to be created first. However if your application allows changes to the hierarchy, where an item may be re-parented somewhere else, then you cannot rely on this.

注意这仅在每个记录的“id”（问题中的 col1）的值大于该记录的“父 id”（问题中的 col3）时才有效。这通常是这种情况，因为通常需要先创建父级。但是，如果您的应用程序允许更改层次结构，其中一个项目可能会在其他地方重新成为父项，那么您就不能依赖于此。

This is my query in case it helps someone; note it does not work with the given question because the data does not follow the required structure described above.

这是我的查询，以防它对某人有帮助；请注意，它不适用于给定的问题，因为数据不遵循上述所需的结构。

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

The difference is that table1is being ordered by col1so that the parent will be after it (since the parent's col1value is lower than the child's).

不同之处在于table1它被排序，col1因此父级将在它之后（因为父级的col1值低于子级的值）。

Answer 3

回答by BoB3K

leftclickben answerworked for me, but I wanted a path from a given node back up the tree to the root, and these seemed to be going the other way, down the tree. So, I had to flip some of the fields around and renamed for clarity, and this works for me, in case this is what anyone else wants too--

leftclickben 答案对我有用，但我想要一条从给定节点返回树到根的路径，而这些路径似乎相反，沿着树向下。因此，为了清楚起见，我不得不翻转一些字段并重新命名，这对我有用，以防其他人也想要--

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

and

和

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;

gives:

给出：

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

Answer 4

回答by Master DJon

If you want to be able to have a SELECT without problems of the parent id having to be lower than child id, a function could be used. It supports also multiple children (as a tree should do) and the tree can have multiple heads. It also ensure to break if a loop exists in the data.

如果您希望能够在没有父 ID 必须低于子 ID 的问题的情况下进行 SELECT，则可以使用函数。它还支持多个子节点（就像一棵树应该做的那样）并且树可以有多个头。如果数据中存在循环，它还可以确保中断。

I wanted to use dynamic SQL to be able to pass the table/columns names, but functions in MySQL don't support this.

我想使用动态 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$$

Here, the table testhas to be modified to the real table name and the columns (ParentId,Id) may have to be adjusted for your real names.

在这里，表test必须修改为真实的表名，并且列（ParentId，Id）可能必须针对您的真实姓名进行调整。

Usage :

用法：

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

Result :

结果：

SQL for test creation :

用于测试创建的 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 : Here is a fiddleto test it yourself. It forced me to change the delimiter using the predefined one, but it works.

编辑：这是一个可以自己测试的小提琴。它迫使我使用预定义的分隔符更改分隔符，但它有效。

Answer 5

回答by Jazmin

Stored procedure is the best way to do it. Because Meherzad's solution would work only if the data follows the same order.

存储过程是最好的方法。因为 Meherzad 的解决方案只有在数据遵循相同顺序时才有效。

If we have a table structure like this

如果我们有这样的表结构

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

It wont work. SQL Fiddle Demo

它不会工作。 SQL Fiddle Demo

Here is a sample procedure code to achieve the same.

这是实现相同目的的示例程序代码。

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;

Answer 6

回答by patrick

Building off of Master DJon

建立在 DJon 大师之上

Here is simplified function which provides the added utility of returning depth (in case you want to use logic to include the parent task or search at a specific depth)

这是简化的函数，它提供了返回深度的附加实用程序（如果您想使用逻辑来包含父任务或在特定深度搜索）

DELIMITER $$
FUNCTION `childDepth`(pParentId INT, pId INT) RETURNS int(11)
    READS SQL DATA
    DETERMINISTIC
BEGIN
DECLARE depth,curId int;
SET depth = 0;
SET curId = pId;

WHILE curId IS not null AND curId <> pParentId DO
    SELECT ParentId from test where id=curId limit 1 into curId;
    SET depth = depth + 1;
END WHILE;

IF curId IS NULL THEN
    set depth = -1;
END IF;

RETURN depth;
END$$

Usage:

用法：

select * from test where childDepth(1, id) <> -1;

如何在 MySQL 中进行递归 SELECT 查询？

提问by Tum

采纳答案by Meherzad

`SQL FIDDLE Demo`:

`SQL FIDDLE Demo`：

回答by leftclickben

回答by BoB3K

回答by Master DJon

回答by Jazmin

回答by patrick

相关推荐

最近更新

标签

如何在 MySQL 中进行递归 SELECT 查询？

提问by Tum

采纳答案by Meherzad

SQL FIDDLE Demo:

SQL FIDDLE Demo：

回答by leftclickben

回答by BoB3K

回答by Master DJon

回答by Jazmin

回答by patrick

相关推荐

MySQL 在同一列上使用多个 WHERE 条件进行选择

如何更改数据库的 MySQL 日期格式？

MySQL 错误 1452：无法添加或更新子行：外键约束失败

mysql show processlist 列出了许多进程 sleep 和 info = null？

相关推荐

最近更新

标签

`SQL FIDDLE Demo`:

`SQL FIDDLE Demo`：