时间:2021-07-01 10:21:17 帮助过:3人阅读
mysql本身不支持递归语法,但可通过自连接变相实现一些简单的递归
- --递归小方法:临时表和普通表的不同方法
- --这题使用的是2次临时表查询父节点的递归
- 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)
上面的方法因为由于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);
- /*
- +------+----------+------+-------+
- | id | parentid | levv | ppath |
- +------+----------+------+-------+
- | 1 | 0 | 1 | 1 |
- | 4 | 0 | 1 | 4 |
- | 2 | 1 | 2 | 12 |
- | 3 | 1 | 2 | 13 |
- | 5 | 2 | 3 | 125 |
- | 7 | 3 | 3 | 137 |
- | 6 | 5 | 4 | 1256 |
- +------+----------+------+-------+*/
以上就是MySQL关于递归的一个问题的详细内容,更多请关注Gxl网其它相关文章!