一. 表结构
二.
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
三。调用结果