mysql 中的数据实现递归查询

一. 表结构

二.

create procedure Pro_GetUnderOrge5(in idd varchar(36))

begin
declare lev int;
set lev=1;
drop table if exists tmp1;
    CREATE TABLE tmp1(vid VARCHAR(5),vname varchar(10),vpid varchar(5),vlevel varchar(5),levv INT);
    INSERT tmp1 SELECT vid,vname,vpid,vlevel,1 FROM digui WHERE vpid=idd;
while  row_count()>0
do
     set lev=lev+1;
     INSERT tmp1 SELECT t.vid,t.vname,t.vpid,t.vlevel,lev from digui t join tmp1 a on t.vpid=a.vid AND levv=lev-1;
end while ;
    INSERT tmp1 SELECT vid,vname,vpid,vlevel,0 FROM digui WHERE vid=idd;
     SELECT * FROM tmp1;
end

三。调用结果