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

我得到一张下表：

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   | o   | 2
2   | 0   | 8

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

在这篇文章中有针对您问题的解决方案stackoverflow.com/questions/14658378/recursive-mysql-select

#1 楼

编辑

@leftclickben提到的解决方案也是有效的。
我们也可以使用存储过程来实现相同的功能。我们正在使用临时表存储输出结果，并且由于临时表是基于会话的，因此不会有关于输出数据不正确的任何问题。

SQL FIDDLE Demo

尝试以下查询：

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

注意parent_id的值应小于child_id才能使此解决方案起作用。

人们将这个答案标记为最佳解决方案，因为类似问题的其他一些解决方案（关于mysql中的递归选择）也相当复杂，因为它需要创建表并将数据插入其中。这个解决方案非常优雅。

–胃
13年5月13日下午4:18

只需注意他的解决方案，就没有循环类型依赖性，它将进入无限循环，而另一件事它只会找到1个该col3类型的记录，因此，如果有多个记录，它将无法工作。

–梅赫扎德
13年5月13日在4:54

@HamidSarfraz现在可以使用sqlfiddle.com/#!2/74f457/14。这将为您工作。随着顺序搜索的进行，id将始终比父级具有更大的值，因为需要先创建父级。请告知您是否需要任何其他详细信息。

–梅赫扎德
2015年2月1日下午5:39

这不是解决方案。这只是表扫描的幸运副作用。请仔细阅读@leftclickben的答案，否则您将像我一样浪费很多时间。

– Jaeheung
15年4月10日在21:53

Tum我知道递归SQL是如何工作的。 MySQL尚未实现递归CTE，因此您提供的链接中的一个可行选择是（使用存储过程/函数）。另一个是使用mysql变量。但是，这里的答案不是优雅，而是相反，只是可怕。它没有显示递归SQL。如果在您的情况下有效，则@jaehung正确指出，这只是偶然。而且我不介意可怕的答案。我只是投票给他们。但是，我想，在+50时，这是一个可怕的答案。

–超立方体ᵀᴹ
2015年11月30日在11:02

#2 楼

@Meherzad接受的答案仅在数据按特定顺序排列时有效。它恰好适用于OP问题中的数据。就我而言，我必须对其进行修改才能使用我的数据。（问题中的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值低于孩子的q4312079q）。

是的，如果一个孩子有2个父母，那么它可能不会两个都选

–胃
2014年7月23日15:25

谢啦。 Teamworek在这篇文章中做到了！当我更改@pv的值时，它可以正常工作。那正是我一直在寻找的东西。

– Mohamed Ennahdi El Idrissi
2015年9月14日12:55在

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

– qdev
15-10-22在12:27

我创建了一个自定义函数，该函数使用group_concat生成树路径，现在我可以将每行的列值作为参数发送;）

– qdev
15-10-22在18:43

您如何看待我发布的新答案？并不是说您的系统不好，但是我只想选择一个可以支持父ID>子ID的SELECT。

– DJon大师
16年6月23日在13:29

#3 楼

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，如果ID不一定是“ order”，则可以正常工作。如果沿链的父母身份证比其子女的身份证更高，这似乎不起作用？例如。链1> 120> 112仅返回（（112，120）），而2> 22> 221返回完整链（（221,22），（22,2），（2，null））

– Tomer Cagan
16年5月4日在12:29

已经有一段时间了，但是我想我记得在原始答案中读到，如果项目ID的顺序不正确，这是行不通的，如果ID是自动递增键，通常就不成问题了。

– BoB3K
16年5月17日在16:51

它运作良好，我在我的网站上使用它...这里的问题是无法订购结果ASC。 1 3 6我在php中使用array_reverse（）代替.....任何sql解决方案？

– joe
18-3-21在13:13

#4 楼

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

这里是实现此目的的示例过程代码。

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

这是一个可靠的解决方案，我在使用时没有遇到任何麻烦。您是否可以在其他方向上帮助我，例如，沿着树-我找到了父ID == inputNo的所有行，但是许多ID可能有一个父ID。

–密耳
15-10-25在23:55

#5 楼

如果您希望能够进行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');

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

#6 楼

在主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;

编程黑洞网