如何在 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查询来做到这一点？

115669 次浏览
小开
最佳答案

剪辑

@ 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，这种解决方案的工作。

小开

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

如果我们有这样的表结构

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

它不工作。 译自: 美国《每日邮报》网站(http://sqlfiddle.com/# ! 2/3551b/1)

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

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;
小开

@ 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值比子代的值低)。

小开

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

如果希望能够使用 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;

结果:

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');

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

小开

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

提交答案