create table `people` (
`id` int(11) not null,
`name` varchar(50) null default null,
`pid` int(11) not null default '0',
primary key (`id`)
);
create definer=`root`@`%` procedure `getchildren`(in `parentid` int)
language sql
not deterministic
contains sql
sql security definer
comment '获取某人所有后代'
begin
#存放结果的临时表
drop table if exists children;
create temporary table children select 0 plevel,p.* from `people` p where id=parentid;
#存放中间结果的临时表
drop table if exists tem;
create temporary table tem select id from `people` limit 0;
#逐级填充后代
set @plevel=1;
repeat
#清空上次数据
truncate table tem;
#将当前level的后代id放入临时表
insert into tem select p.id from `people` p, children c
where p.pid=c.id and c.plevel=(@plevel-1);
#将当前level的后代数据塞入结果临时表
insert into children select @plevel plevel,p.* from `people` p, tem t
where p.id=t.id;
set @plevel=@plevel+1;
until not exists (select * from tem) or @plevel > 10
end repeat;
#调整表结构，删除临时列和不需要的数据
alter table children drop column plevel;
delete from children where id=parentid;
#返回结果
select * from children;
end
