MySQL關于遞歸的一個問題

--遞歸小方法：臨時表和普通表的不同方法  --這題使用的是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))  begin  declare?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?)  do  truncate?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?select  1,?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))  begin  declare?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()>0  do    set?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?|  +------+----------+------+-------+*/