· 8 years ago · Jul 16, 2018, 05:36 PM
1
2drop table if exists employee;
3
4create table employee
5(
6emp_id int unsigned not null auto_increment primary key,
7name varchar(32) not null,
8boss_id int unsigned null
9);
10
11insert into employee (name, boss_id) values
12('foo',null),
13('ali later',1), ('megan fox',1),
14('jessica alba',2), ('eva longoria',2),
15('keira knightley',3), ('liv tyler',3),
16('sophie marceau',5);
17
18
19delimiter ;
20
21drop procedure if exists employee_hier;
22
23delimiter #
24
25create procedure employee_hier
26(
27in p_emp_id int unsigned
28)
29begin
30
31declare p_done tinyint unsigned default(0);
32declare p_depth tinyint unsigned default(0);
33
34create temporary table hier(
35 boss_id int unsigned,
36 emp_id int unsigned,
37 depth tinyint unsigned
38)engine = memory;
39
40insert into hier values (null, p_emp_id, p_depth);
41
42create temporary table emps engine=memory select * from hier;
43
44while p_done <> 1 do
45
46 if exists( select 1 from employee e inner join hier on e.boss_id = hier.emp_id and hier.depth = p_depth) then
47
48 insert into hier select e.boss_id, e.emp_id, p_depth + 1
49 from employee e inner join emps on e.boss_id = emps.emp_id and emps.depth = p_depth;
50
51 set p_depth = p_depth + 1;
52
53 truncate table emps;
54 insert into emps select * from hier where depth = p_depth;
55
56 else
57 set p_done = 1;
58 end if;
59
60end while;
61
62select
63 e.emp_id,
64 e.name as emp_name,
65 b.emp_id as boss_emp_id,
66 b.name as boss_name,
67 hier.depth
68from
69 hier
70inner join employee e on hier.emp_id = e.emp_id
71inner join employee b on hier.boss_id = b.emp_id;
72
73drop temporary table if exists hier;
74drop temporary table if exists emps;
75
76end #
77
78delimiter ;
79
80/*
81
82select * from employee;
83
84emp_id name boss_id
85====== ==== =======
861 foo null
872 ali later 1
883 megan fox 1
894 jessica alba 2
905 eva longoria 2
916 keira knightley 3
927 liv tyler 3
938 sophie marceau 5
94
95call employee_hier(1);
96
97emp_id emp_name boss_emp_id boss_name depth
98====== ======== =========== ========= =====
992 ali later 1 foo 1
1003 megan fox 1 foo 1
1014 jessica alba 2 ali later 2
1025 eva longoria 2 ali later 2
1036 keira knightley 3 megan fox 2
1047 liv tyler 3 megan fox 2
1058 sophie marceau 5 eva longoria 3
106
107call employee_hier(3);
108
109emp_id emp_name boss_emp_id boss_name depth
110====== ======== =========== ========= =====
1116 keira knightley 3 megan fox 1
1127 liv tyler 3 megan fox 1
113*/
114
115
116
117drop table if exists category;
118
119create table category
120(
121cat_id smallint unsigned not null auto_increment primary key,
122name varchar(32) not null,
123parent_cat_id smallint unsigned null
124);
125
126insert into category (name, parent_cat_id) values
127('root',null),
128('cat 1',1),
129('cat 2',1),
130('cat 3',1),
131('cat 1-1',2),
132('cat 1-2',2),
133('cat 1-3',2),
134('cat 2-1',3),
135('cat 2-2',3),
136('cat 2-3',3),
137('cat 3-1',4),
138('cat 3-2',4),
139('cat 3-3',4),
140('cat 1-1-1',5),
141('cat 1-1-2',5),
142('cat 1-1-3',5),
143('cat 2-1-1',8),
144('cat 2-1-2',8),
145('cat 2-1-3',8),
146('cat 3-1-1',11),
147('cat 3-1-2',11),
148('cat 3-1-3',11);
149
150delimiter ;
151
152drop procedure if exists category_hier;
153
154delimiter #
155
156create procedure category_hier
157(
158in p_root_id int unsigned
159)
160begin
161
162declare p_done tinyint unsigned default(0);
163declare p_depth tinyint unsigned default(0);
164
165create temporary table hier(
166 id int unsigned auto_increment primary key,
167 parent_id int unsigned null,
168 child_id int unsigned not null,
169 depth tinyint unsigned
170)engine = memory;
171
172insert into hier (parent_id, child_id, depth) values (null, p_root_id, p_depth);
173
174create temporary table cats engine=memory select * from hier;
175
176while p_done <> 1 do
177
178 if exists( select 1 from category c inner join hier on c.parent_cat_id = hier.child_id and hier.depth = p_depth) then
179
180 insert into hier (parent_id, child_id, depth) select c.parent_cat_id, c.cat_id, p_depth + 1
181 from category c inner join cats on c.parent_cat_id = cats.child_id and cats.depth = p_depth;
182
183 set p_depth = p_depth + 1;
184
185 truncate table cats;
186 insert into cats (parent_id, child_id, depth) select parent_id, child_id, depth from hier where depth = p_depth;
187
188 else
189 set p_done = 1;
190 end if;
191
192end while;
193
194select
195 hier.id,
196 c.cat_id,
197 c.name as cat_name,
198 p.cat_id as parent_cat_id,
199 p.name as parent_name,
200 hier.depth
201from
202 hier
203inner join category c on hier.child_id = c.cat_id
204inner join category p on hier.parent_id = p.cat_id;
205
206drop temporary table if exists hier;
207drop temporary table if exists cats;
208
209end #
210
211delimiter ;
212
213-- call category_hier(2)