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

Question

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

mysqlsqlquery-optimizationrecursive-query

81

我有一个以下的表格：

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

如果用户搜索 "1"，程序将查找具有 "1" 的 col1，然后获取col3中的值 "5"，然后程序将继续在col1中搜索 "5" 并获得col3中的值 "3"，以此类推。因此，它将打印出：

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

如果用户搜索"6"，它将打印出：

6

6   | o   | 2
2   | 0   | 8

如何构建一个SELECT查询来实现这个功能？

- Tum

这篇文章中有你问题的解决方案 https://dev59.com/3W3Xa4cB1Zd3GeqPk_oF - medina

6个回答

53

@Meherzad发表的被接受的答案仅适用于数据按特定顺序排列的情况。它碰巧适用于OP问题中的数据。在我的情况下，我不得不修改它以使其与我的数据配合使用。

请注意，只有当每个记录的"id"（问题中的col1）具有大于该记录的"parent 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 值低于子级）。

- leftclickben

你说得对，而且如果一个孩子有两个父母，那么它可能不能选择两个。 - Tum

谢谢，Teamwork 在这篇文章中发挥了作用！当我改变 @pv 的值时，我终于让它工作了。这正是我一直在寻找的。 - Mohamed Ennahdi El Idrissi

如果我想将此作为父ID的group_concat列用于更大选择中每行的父ID（这意味着@pv变量的值对于每行都是动态的），该怎么办？子查询中的连接不知道主列（我尝试连接的列），使用另一个变量也不起作用（始终返回NULL）。 - qdev

我已经创建了一个自定义函数，它使用group_concat生成树路径，并且现在我可以将每行的列值作为参数发送。;) - qdev

你觉得我发布的新答案怎么样？不是说你的不好，但我想要一个仅支持父ID>子ID的SELECT。 - Master DJon

20

leftclickben的回答对我有用，但我想要从给定节点回到根节点的路径，而这些似乎是朝着另一个方向，往下走。所以，我不得不翻转一些字段并进行重命名以增加清晰度，这对我有效，如果其他人也需要这样做，那么这就是我想要的方式。

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

并且

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;

提供：

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

- BoB3K

@BoB3K，如果ID不一定“有序”，这样做行得通吗？似乎在上下级关系中，如果父级ID比子级高时，它不起作用？例如，链1 > 120 > 112只会返回((112, 120))，而2 > 22 > 221则返回完整的链((221,22),(22,2),(2,null))。 - Tomer Cagan

已经有一段时间了，但我记得在原始答案中读到过，如果项目ID不按顺序排列，则此方法无法正常工作，如果ID是自动递增键，则通常不会出现问题。 - BoB3K

它运行良好，我在我的网站上使用它......问题在于无法按升序排序结果 1 3 6。我改用 PHP 中的 array_reverse() 函数.....有没有 SQL 的解决方案？ - joe

9

如果您想要进行SELECT操作而无需担心父ID必须低于子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;

结果：

测试创建的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');

编辑：这里有一个SQL Fiddle，可以让你自己测试。它强制我使用预定义的分隔符，但是它可以正常工作。

- Master DJon

9

存储过程是最好的方法。因为Meherzad的解决方案只适用于数据遵循相同的顺序。

如果我们有这样的表结构

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

无法运行。 SQL Fiddle样例演示

以下是一个实现相同功能的过程代码的示例。

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;

- Jazmin

这是一个强大的解决方案，我正在无障碍地使用它。您能否帮助我在另一个方向上进行，即向下遍历树 - 我找到所有父ID == inputNo的行，但许多ID可能有一个父ID。 - mils

0

在Master DJon的基础上构建

这里是一个简化的函数，它提供了额外的实用功能，可以返回深度（以防您想使用逻辑来包括父任务或在特定深度搜索）

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

使用方法：

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

- patrick

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Meherzad · Accepted Answer

编辑

@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演示`:

| COL1 | COL2 | COL3 |
+------+------+------+
|    1 |    a |    5 |
|    5 |    d |    3 |
|    3 |    k |    7 |

注意
为使本方案生效，parent_id 的值应小于 child_id。

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

SQL FIDDLE演示:

`SQL FIDDLE演示`: