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

1 drop table if exists employee;
2
3 create table employee
4 (
5 emp_id smallint unsigned not null auto_increment primary key ,
6 name varchar ( 32 ) not null ,
7 boss_id smallint unsigned null ,
8 key boss_id_idx(boss_id)
9 )engine = innodb;
10
11
12 insert into employee (name, boss_id) values
13 ( ' foo ' , null ),
14 ( ' ali later ' , 1 ), ( ' megan fox ' , 1 ),
15 ( ' jessica alba ' , 2 ), ( ' eva longoria ' , 2 ),
16 ( ' keira knightley ' , 3 ), ( ' liv tyler ' , 3 ),
17 ( ' sophie marceau ' , 5 );
18
19
20 delimiter ;
21
22 drop procedure if exists employee_hier;
23
24 delimiter #
25
26 create procedure employee_hier
27 (
28 in p_emp_id smallint unsigned
29 )
30 begin
31
32 declare p_done tinyint unsigned default ( 0 );
33 declare p_depth smallint unsigned default ( 0 );
34
35 create temporary table hier(
36 boss_id smallint unsigned,
37 emp_id smallint unsigned,
38 depth smallint unsigned
39 )engine = memory;
40
41 insert into hier values ( null , p_emp_id, p_depth);
42
43 /* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */
44
45 create temporary table emps engine = memory select * from hier;
46
47 while p_done <> 1 do
48
49 if exists ( select 1 from employee e inner join hier on e.boss_id = hier.emp_id and hier.depth = p_depth) then
50
51 insert into hier select e.boss_id, e.emp_id, p_depth + 1
52 from employee e inner join emps on e.boss_id = emps.emp_id and emps.depth = p_depth;
53
54 set p_depth = p_depth + 1 ;
55
56 truncate table emps;
57 insert into emps select * from hier where depth = p_depth;
58
59 else
60 set p_done = 1 ;
61 end if ;
62
63 end while ;
64
65 select
66 e.emp_id,
67 e.name as emp_name,
68 b.emp_id as boss_emp_id,
69 b.name as boss_name,
70 hier.depth
71 from
72 hier
73 inner join employee e on hier.emp_id = e.emp_id
74 inner join employee b on hier.boss_id = b.emp_id;
75
76 drop temporary table if exists hier;
77 drop temporary table if exists emps;
78
79 end #
80
81 delimiter ;
82
83 /*
84
85 select * from employee;
86
87 emp_id name boss_id
88 ====== ==== =======
89 1 foo null
90 2 ali later 1
91 3 megan fox 1
92 4 jessica alba 2
93 5 eva longoria 2
94 6 keira knightley 3
95 7 liv tyler 3
96 8 sophie marceau 5
97
98 call employee_hier(1);
99
100 emp_id emp_name boss_emp_id boss_name depth
101 ====== ======== =========== ========= =====
102 2 ali later 1 foo 1
103 3 megan fox 1 foo 1
104 4 jessica alba 2 ali later 2
105 5 eva longoria 2 ali later 2
106 6 keira knightley 3 megan fox 2
107 7 liv tyler 3 megan fox 2
108 8 sophie marceau 5 eva longoria 3
109
110 call employee_hier(3);
111
112 emp_id emp_name boss_emp_id boss_name depth
113 ====== ======== =========== ========= =====
114 6 keira knightley 3 megan fox 1
115 7 liv tyler 3 megan fox 1
116 */

摘自国外网站：http://pastie.org/1056977

转载于:https://www.cnblogs.com/sunss/archive/2011/06/14/2080617.html