問題描述
我得到了下表:
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_idvalue should be less than thechild_idfor this solution to work.
這篇關于如何在 MySQL 中進行遞歸 SELECT 查詢?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！