如何在MySQL中执行递归SELECT查询? [英] How to do the Recursive SELECT query in MySQL?

问题描述

我有一张下表:

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

如果用户搜索"1"，程序将查看具有"1"的col1，然后它将在col3"5"中获得一个值，然后程序将继续搜索"5". col1中的"，它将在col3中获得"3"，依此类推.因此它将打印出来:

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

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

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

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

6   | o   | 2
2   | 0   | 8

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

How to build a SELECT query to do that?

推荐答案

编辑

@leftclickben提到的解决方案也是有效的. 我们也可以对它使用存储过程.

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

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

<罢工> 尝试以下查询:

SQL FIDDLE Demo

Try this query:

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，此解决方案才能起作用.

Note
parent_id value should be less than the child_id for this solution to work.

这篇关于如何在MySQL中执行递归SELECT查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！