postgresql之递归查询

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

 

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

 

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

 

递归查询评估

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

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

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

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

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

可选的RECURSIVE修饰符从仅有的语法便利性到一个完成事情的特性的改变。 使用RECURSIVE，一个WITH查询可以引用它自己的输出。

 

一个简单的例子就是查询从1加到100的和：

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

当使用递归查询的时候，确保查询的递归部分最终不会返回元组是很重要的，否则查询将会循环下去。

 再看个例子：

with recursive cte as (
            select * from cn_product_menu where menu_id=123 union all
            select b.* from cn_product_menu b inner join cte c on b.parent_id=c.menu_id)
            select menu_id from cte;

 得出表cn_product_menu中menu_id=123下所有的子（孙）menu_id