drop
table
if exists test;
create
table
test(
id
varchar
(100),
name
varchar
(20),
parentid
varchar
(100)
);
insert
test
select
'13ed38f1-3c24-dd81-492f-673686dff0f3'
,
'大学教师'
,
'37e2ea0a-1c31-3412-455a-5e60b8395f7d'
union
all
select
'1ce203ac-ee34-b902-6c10-c806f0f52876'
,
'小学教师'
,
'37e2ea0a-1c31-3412-455a-5e60b8395f7d'
union
all
select
'37e2ea0a-1c31-3412-455a-5e60b8395f7d'
,
'教师'
,
null
union
all
select
'c877b7ea-4ed3-f472-9527-53e1618cb1dc'
,
'高数老师'
,
'13ed38f1-3c24-dd81-492f-673686dff0f3'
union
all
select
'ce50a471-2955-00fa-2fb7-198f6b45b1bd'
,
'中学教师'
,
'37e2ea0a-1c31-3412-455a-5e60b8395f7d'
;
delimiter $$
create
procedure
usp_ser(
in
idd
varchar
(100))
begin
declare
lev
int
;
set
lev=1;
drop
table
if exists tmp1;
drop
table
if exists tmp2;
CREATE
TEMPORARY
TABLE
tmp1(id
varchar
(100),
name
varchar
(20),parentid
varchar
(100),levv
int
);
CREATE
TEMPORARY
TABLE
tmp2(pid
varchar
(100));
insert
tmp2
select
parentid
from
test
where
id=idd;
insert
tmp1
select
t.* , lev
from
test t
join
tmp2 a
on
t.id=a.pid;
while exists(
select
1
from
tmp2 )
do
truncate
tmp2;
set
lev=lev+1;
insert
tmp2
select
t.id
from
test t
join
tmp1 a
on
t.id=a.parentid
and
a.levv=lev-1;
insert
tmp1
select
t.*,lev
from
test t
join
tmp2 a
on
t.id=a.pid;
end
while ;
select
id,
name
,parentid
from
tmp1;
end
;
$$
delimiter ;
call usp_ser(
'c877b7ea-4ed3-f472-9527-53e1618cb1dc'
);
+
| id |
name
| parentid |
+
| 13ed38f1-3c24-dd81-492f-673686dff0f3 | 大学教师 | 37e2ea0a-1c31-3412-455a-5e60b8395f7d |
| 37e2ea0a-1c31-3412-455a-5e60b8395f7d | 教师 |
NULL
|
+
call usp_ser(
'13ed38f1-3c24-dd81-492f-673686dff0f3'
);
+
| id |
name
| parentid |
+
| 37e2ea0a-1c31-3412-455a-5e60b8395f7d | 教师 |
NULL
|
+
call usp_ser(
'37e2ea0a-1c31-3412-455a-5e60b8395f7d'
);
Empty
set
(0.02 sec)