如何在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查询来执行此操作？

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。

@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

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

如果我们有这样的表结构

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;

如果您希望能够拥有一个 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');

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

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

`SQL FIDDLE Demo` ：

相关内容

最新更新

热门标签：

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

SQL FIDDLE Demo ：

相关内容

最新更新

热门标签：

`SQL FIDDLE Demo` ：