MySQL存储过程递归实现_（转）MySQL用存储过程实现递归查询（一）

drop table if existsemployee;create tableemployee

(

emp_idsmallint unsigned not null auto_increment primary key,

namevarchar(32) not null,

boss_idsmallint unsigned null,keyboss_id_idx(boss_id)

)engine=innodb;insert into employee (name, boss_id) values('foo',null),

('ali later',1), ('megan fox',1),

('jessica alba',2), ('eva longoria',2),

('keira knightley',3), ('liv tyler',3),

('sophie marceau',5);

delimiter ;drop procedure if existsemployee_hier;

delimiter #create procedureemployee_hier

(in p_emp_id smallintunsigned

)begin

declare p_done tinyint unsigned default(0);declare p_depth smallint unsigned default(0);create temporary tablehier(

boss_idsmallintunsigned,

emp_idsmallintunsigned,

depthsmallintunsigned

)engine=memory;insert into hier values (null, p_emp_id, p_depth);/*http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html*/

create temporary table emps engine=memory select * fromhier;while p_done <> 1doif exists( select 1 from employee e inner join hier on e.boss_id = hier.emp_id and hier.depth = p_depth) then

insert into hier select e.boss_id, e.emp_id, p_depth + 1

from employee e inner join emps on e.boss_id = emps.emp_id and emps.depth =p_depth;set p_depth = p_depth + 1;truncate tableemps;insert into emps select * from hier where depth =p_depth;else

set p_done = 1;end if;end while;selecte.emp_id,

e.nameasemp_name,

b.emp_idasboss_emp_id,

b.nameasboss_name,

hier.depthfromhierinner join employee e on hier.emp_id =e.emp_idinner join employee b on hier.boss_id =b.emp_id;drop temporary table if existshier;drop temporary table if existsemps;end#

delimiter ;/*select * from employee;

emp_id name boss_id

====== ==== =======

1 foo null

2 ali later 1

3 megan fox 1

4 jessica alba 2

5 eva longoria 2

6 keira knightley 3

7 liv tyler 3

8 sophie marceau 5

call employee_hier(1);

emp_id emp_name boss_emp_id boss_name depth

====== ======== =========== ========= =====

2 ali later 1 foo 1

3 megan fox 1 foo 1

4 jessica alba 2 ali later 2

5 eva longoria 2 ali later 2

6 keira knightley 3 megan fox 2

7 liv tyler 3 megan fox 2

8 sophie marceau 5 eva longoria 3

call employee_hier(3);

emp_id emp_name boss_emp_id boss_name depth

====== ======== =========== ========= =====

6 keira knightley 3 megan fox 1

7 liv tyler 3 megan fox 1*/