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

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (68)

我有一个下表:

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

如果用户搜索“1”,程序将查看col1中有“1”的值col3“5”,则程序将继续在col1它就会有“3”col3等等。所以它会打印出来:

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

如果用户搜索“6”,它将打印出:

6   | o   | 2
2   | 0   | 8

如何构建一个SELECT查询来做这个吗?

提问于
用户回答回答于
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

返回

| COL1 | COL2 | COL3 |
+------+------+------+
|    1 |    a |    5 |
|    5 |    d |    3 |
|    3 |    k |    7 |
用户回答回答于
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

扫码关注云+社区

领取腾讯云代金券