postgresql之递归查询

mysql本身不支持递归语法,但可通过自连接变相实现一些简单的递归

在开发中经常会遇到需要递归查询子节点的情况,之前用的是mysql,没有具体的相对应的功能。

--递归小方法:临时表和普通表的不同方法--这题使用的是2次临时表查询父节点的递归 drop table if exists test;create table test,name varchar,parentid varchar;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_serbegindeclare lev int;set lev=1;drop table if exists tmp1;drop table if exists tmp2;CREATE TEMPORARY TABLE tmp1,name varchar,parentid varchar;CREATE TEMPORARY TABLE tmp2;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 existsdotruncate 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 

 

上面的方法因为由于MySQL中不允许在同一语句中对临时表多次引用,所以用2次临时表下面给个一次性用普通表完成的
查询子节点的递归查询

但在postgresql中有一个强大的with查询语句。

drop table if exists test;create table test;insert test select1, 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_serbegindeclare lev int;set lev=1;drop table if exists tmp1;CREATE TABLE tmp1(id INT,parentid INT ,levv INT,ppath VARCHAR;INSERT tmp1 SELECT *,lev,id FROM test WHERE parentid=idd; while row_count()>0doset lev=lev+1;insert tmp1 select t.*,lev,concat 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; /*+------+----------+------+-------+| 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 |+------+----------+------+-------+*/

 

WITH提供了一种在更大的SELECT查询中编写子查询的方式。这个通常称为公共表表达式或CTEs的子查询可以认为是定义存在于查询中的临时表。这个特性的一个应用是用于分解复杂查询为简单的部分。

 

递归查询评估

  1. 评估无递归术语。使用UNION(并不是UNION ALL),去除重复的行。包括在递归查询结果中所有剩余的行,并将它们放入临时的工作表

  2. 只要工作表不为空,那么将重复这些步骤:

    1. 评价递归术语,为递归自我参照替换当前工作表内容。
      UNION(并不是UNION ALL),去除重复的行和与以前
      结果行重复的行。
      包括所有在递归查询结果中剩余的行,并将它们放入一个临时的中间表

    2. 以中间表的内容替换工作表的内容,然后清空中间表。

Note:
严格的说,该过程是迭代而不是递归,但是RECURSIVE是通过SQL标准委员会选择的术语

发表评论

电子邮件地址不会被公开。 必填项已用*标注