MySQL對(duì)于遞歸的一個(gè)問(wèn)題

mysql本身不支持遞歸語(yǔ)法，但可通過(guò)自連接變相實(shí)現(xiàn)一些簡(jiǎn)單的遞歸

--遞歸小方法：臨時(shí)表和普通表的不同方法
--這題使用的是2次臨時(shí)表查詢(xún)父節(jié)點(diǎn)的遞歸 

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', '大學(xué)教師', '37e2ea0a-1c31-3412-455a-5e60b8395f7d' union all select 
'1ce203ac-ee34-b902-6c10-c806f0f52876','小學(xué)教師', '37e2ea0a-1c31-3412-455a-5e60b8395f7d' union all select 
'37e2ea0a-1c31-3412-455a-5e60b8395f7d', '教師' ,      null                union all select 
'c877b7ea-4ed3-f472-9527-53e1618cb1dc', '高數(shù)老師', '13ed38f1-3c24-dd81-492f-673686dff0f3' union all select 
'ce50a471-2955-00fa-2fb7-198f6b45b1bd', '中學(xué)教師', '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   大學(xué)教師   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)

上面的方法因?yàn)橛捎贛ySQL中不允許在同一語(yǔ)句中對(duì)臨時(shí)表多次引用，所以用2次臨時(shí)表
下面給個(gè)一次性用普通表完成的 查詢(xún)子節(jié)點(diǎn)的遞歸查詢(xún)

核心代碼

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  
+------+----------+------+-------+*/

以上就是MySQL關(guān)于遞歸的一個(gè)問(wèn)題的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！