mysql递归找到最上级父类_mysql 递归查找父类

drop function getParentList ;

create  function getParentList(subId varchar(100),iv_year int)

Returns varchar(1000)

begin

declare fid varchar(100) default '';

declare temp varchar(1000) default '';

declare str varchar(1000) default '' ;

declare i_flag  int default 0 ;

select count(1) into i_flag from tb_jd_0_f_yxnbdwszylb where dwcode=subId  and year= iv_year;

if i_flag>0 then

while subId is not null and iv_year is not null do

select sjdwid,sdwmc into fid,temp from tb_jd_0_f_yxnbdwszylb where dwcode=subId  and year= iv_year;

if fid is not null and  fid != '' then

if length(str)<=0 then

set str= temp;

else

set str = concat(temp,'',str);

end if;

set subId = fid;

else

set subId = fid;

end if;

end while;

-- 由于最后一次不会执行上面的循环所有就单独执行

select sjdwid,sdwmc into fid,temp from tb_jd_0_f_yxnbdwszylb where dwcode=subId and year= iv_year ;

if length(str)<=0 then

set str= temp;

else

set str = concat(temp,'',str);

end if;

end if;

return str;

end;