[摘要]+--------------------------------------+------+----------+call usp_ser(37e2ea0a-1c31-3412-455a-5e60...
+--------------------------------------+------+----------+
call usp_ser('37e2ea0a-1c31-3412-455a-5e60b8395f7d');
Empty set (0.02 sec)
上面的方法因为由于MySQL中不允许在同一语句中对临时表多次引用，所以用2次临时表
下面给个一次性用普通表完成的 查询子节点的递归查询
核心代码
drop table if exists test;
create table test(
id INT,
parentid INT
);
insert test select
1, 0 UNION ALL SELECT
2, 1 UNION ALL SELECT
3, 1 UNION ALL SELECT
4, 0 UNION ALL SELECT
5, 2 UNION ALL SELECT
6, 5 UNION ALL SELECT
7, 3 ;
Go
delimiter $$
create procedure usp_ser(in idd varchar(100))
begin
declare lev int;
set lev=1;
drop table if exists tmp1;
CREATE TABLE tmp1(id INT,parentid INT ,levv INT,ppath VARCHAR(1000));
INSERT tmp1 SELECT *,lev,id FROM test WHERE parentid=idd;
while row_count()>0
do
set lev=lev+1;
insert tmp1 select t.*,lev,concat(a.ppath,t.id) from test t join tmp1 a on t.parentid=a.id AND levv=LEV-1;
end while ;
SELECT * FROM tmp1;
end;
$$
delimiter ;
call usp_ser(0);
/*
+------+----------+------+-------+
关键词：MySQL对于递归的一个问题