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

转载自：http://www.cnblogs.com/sunss/archive/2011/06/14/2080617.html

drop table if exists employee;
 
 create table employee
 (
 emp_id smallint unsigned not null auto_increment primary key,
 name varchar(32) not null,
 boss_id smallint unsigned null,
 key boss_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 exists employee_hier;
 
 delimiter #
 
 create procedure employee_hier
 (
 in p_emp_id smallint unsigned
 )
 begin
 
 declare p_done tinyint unsigned default(0);
 declare p_depth smallint unsigned default(0);
 
 create temporary table hier(
  boss_id smallint unsigned, 
  emp_id smallint unsigned, 
  depth smallint unsigned
 )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 * from hier;
 
 while p_done <> 1 do
 
   if 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 table emps;
     insert into emps select * from hier where depth = p_depth;
 
   else
     set p_done = 1;
   end if;
 
 end while;
 
 select 
  e.emp_id,
  e.name as emp_name,
  b.emp_id as boss_emp_id,
  b.name as boss_name,
  hier.depth
 from 
  hier
 inner join employee e on hier.emp_id = e.emp_id
 inner join employee b on hier.boss_id = b.emp_id;
 
 drop temporary table if exists hier;
 drop temporary table if exists emps;
 
 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
 */

 

谢谢浏览！