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

2021-11-20 00:00:00 sql mysql query-optimization recursive-query

我得到了下表:

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"，以此类推.所以它会打印出来:

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 演示

<打击>试试这个查询:

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

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

相关文章