--遞歸小方法：臨時表和普通表的不同方法--這題使用的是2次臨時表查詢父節點的遞歸 drop table if exists test;create table test(id varchar(100),name varchar(20),parentid varchar(100));insert test select'13ed38f1-3c24-dd81-492f-673686dff0f3', '大學教師', '37e2ea0a-1c31-3412-455a-5e60b8395f7d' union all select '1ce203ac-ee34-b902-6c10-c806f0f52876','小學教師', '37e2ea0a-1c31-3412-455a-5e60b8395f7d' union all select '37e2ea0a-1c31-3412-455a-5e60b8395f7d', '教師' ,      null                union all select 'c877b7ea-4ed3-f472-9527-53e1618cb1dc', '高數老師', '13ed38f1-3c24-dd81-492f-673686dff0f3' union all select 'ce50a471-2955-00fa-2fb7-198f6b45b1bd', '中學教師', '37e2ea0a-1c31-3412-455a-5e60b8395f7d'; delimiter $$ create procedure usp_ser(in idd varchar(100))begindeclare lev int;set lev=1;drop table if exists tmp1;drop table if exists tmp2;CREATE TEMPORARY TABLE tmp1(id varchar(100),name varchar(20),parentid varchar(100),levv int);CREATE TEMPORARY TABLE tmp2(pid varchar(100));insert tmp2 select parentid from test where id=idd;insert tmp1 select t.* , lev from test t join tmp2 a on t.id=a.pid;    while exists(select 1 from tmp2 )dotruncate tmp2;set lev=lev+1;insert tmp2 select t.id from test t join tmp1 a on t.id=a.parentid and a.levv=lev-1;insert tmp1 select t.*,lev from test t join tmp2 a on t.id=a.pid;end while ;select id,name,parentid from tmp1;end;$$ delimiter ;  call usp_ser('c877b7ea-4ed3-f472-9527-53e1618cb1dc');+--------------------------------------+----------+--------------------------------------+| id                  | name   | parentid               |+--------------------------------------+----------+--------------------------------------+| 13ed38f1-3c24-dd81-492f-673686dff0f3 | 大學教師 | 37e2ea0a-1c31-3412-455a-5e60b8395f7d || 37e2ea0a-1c31-3412-455a-5e60b8395f7d | 教師   | NULL                 |+--------------------------------------+----------+--------------------------------------+  call usp_ser('13ed38f1-3c24-dd81-492f-673686dff0f3');+--------------------------------------+------+----------+| id                  | name | parentid |+--------------------------------------+------+----------+| 37e2ea0a-1c31-3412-455a-5e60b8395f7d | 教師 | NULL   |+--------------------------------------+------+----------+  call usp_ser('37e2ea0a-1c31-3412-455a-5e60b8395f7d'); Empty set (0.02 sec)

drop table if exists test;create table test(id INT,parentid INT);insert test select1, 0 UNION ALL SELECT 2, 1 UNION ALL SELECT 3, 1 UNION ALL SELECT 4, 0 UNION ALL SELECT 5, 2 UNION ALL SELECT 6, 5 UNION ALL SELECT 7, 3 ;Go delimiter $$ create procedure usp_ser(in idd varchar(100))begindeclare lev int;set lev=1;drop table if exists tmp1;CREATE TABLE tmp1(id INT,parentid INT ,levv INT,ppath VARCHAR(1000));INSERT tmp1 SELECT *,lev,id FROM test WHERE parentid=idd; while row_count()>0doset lev=lev+1;insert tmp1 select t.*,lev,concat(a.ppath,t.id) from test t join tmp1 a on t.parentid=a.id AND levv=LEV-1; end while ;SELECT * FROM tmp1; end;$$ delimiter ;  call usp_ser(0); /*+------+----------+------+-------+| id  | parentid | levv | ppath |+------+----------+------+-------+|  1 |    0 |  1 | 1   ||  4 |    0 |  1 | 4   ||  2 |    1 |  2 | 12  ||  3 |    1 |  2 | 13  ||  5 |    2 |  3 | 125  ||  7 |    3 |  3 | 137  ||  6 |    5 |  4 | 1256 |+------+----------+------+-------+*/