MySQL之递归小问题
mysql本身不支持递归语法，但可通过自连接变相实现一些简单的递归
--递归小方法：临时表和普通表的不同方法
--这题使用的是2次临时表查询父节点的递归
droptableifexiststest;
createtabletest(
idvarchar(100),
namevarchar(20),
parentidvarchar(100)
);
inserttestselect
'13ed38f1-3c24-dd81-492f-673686dff0f3','大学教师','37e2ea0a-1c31-3412-455a-5e60b8395f7d'unionallselect
'1ce203ac-ee34-b902-6c10-c806f0f52876','小学教师','37e2ea0a-1c31-3412-455a-5e60b8395f7d'unionallselect
'37e2ea0a-1c31-3412-455a-5e60b8395f7d','教师',nullunionallselect
'c877b7ea-4ed3-f472-9527-53e1618cb1dc','高数老师','13ed38f1-3c24-dd81-492f-673686dff0f3'unionallselect
'ce50a471-2955-00fa-2fb7-198f6b45b1bd','中学教师','37e2ea0a-1c31-3412-455a-5e60b8395f7d';
delimiter$$
createprocedureusp_ser(iniddvarchar(100))
begin
declarelevint;
setlev=1;
droptableifexiststmp1;
droptableifexiststmp2;
CREATETEMPORARYTABLEtmp1(idvarchar(100),namevarchar(20),parentidvarchar(100),levvint);
CREATETEMPORARYTABLEtmp2(pidvarchar(100));
inserttmp2selectparentidfromtestwhereid=idd;
inserttmp1selectt.*,levfromtesttjointmp2aont.id=a.pid;
whileexists(select1fromtmp2)
do
truncatetmp2;
setlev=lev+1;
inserttmp2selectt.idfromtesttjointmp1aont.id=a.parentidanda.levv=lev-1;
inserttmp1selectt.*,levfromtesttjointmp2aont.id=a.pid;
endwhile;
selectid,name,parentidfromtmp1;
end;
$$
delimiter;
callusp_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|
+--------------------------------------+----------+--------------------------------------+
callusp_ser('13ed38f1-3c24-dd81-492f-673686dff0f3');
+--------------------------------------+------+----------+
|id|name|parentid|
+--------------------------------------+------+----------+
|37e2ea0a-1c31-3412-455a-5e60b8395f7d|教师|NULL|
+--------------------------------------+------+----------+
callusp_ser('37e2ea0a-1c31-3412-455a-5e60b8395f7d');
Emptyset(0.02sec)
上面的方法因为由于MySQL中不允许在同一语句中对临时表多次引用，所以用2次临时表
下面给个一次性用普通表完成的查询子节点的递归查询
核心代码
droptableifexiststest; createtabletest( idINT, parentidINT ); inserttestselect 1,0UNIONALLSELECT 2,1UNIONALLSELECT 3,1UNIONALLSELECT 4,0UNIONALLSELECT 5,2UNIONALLSELECT 6,5UNIONALLSELECT 7,3; Go delimiter$$ createprocedureusp_ser(iniddvarchar(100)) begin declarelevint; setlev=1; droptableifexiststmp1; CREATETABLEtmp1(idINT,parentidINT,levvINT,ppathVARCHAR(1000)); INSERTtmp1SELECT*,lev,idFROMtestWHEREparentid=idd; whilerow_count()>0 do setlev=lev+1; inserttmp1selectt.*,lev,concat(a.ppath,t.id)fromtesttjointmp1aont.parentid=a.idANDlevv=LEV-1; endwhile; SELECT*FROMtmp1; end; $$ delimiter; callusp_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| +------+----------+------+-------+*/