postgresql之递归查询

ezeke

　　在开发中经常会遇到需要递归查询子节点的情况，之前用的是mysql，没有具体的相对应的功能。
　　 
　　但在postgresql中有一个强大的with查询语句。
　　 
　　WITH提供了一种在更大的SELECT查询中编写子查询的方式。这个通常称为公共表表达式或CTEs的子查询可以认为是定义存在于查询中的临时表。这个特性的一个应用是用于分解复杂查询为简单的部分。
　　 
　　递归查询评估

• 　　评估无递归术语。使用UNION(并不是UNION ALL)，去除重复的行。包括在递归查询结果中所有剩余的行，并将它们放入临时的工作表。
  
  
• 　　只要工作表不为空，那么将重复这些步骤：
  
  
  
  
  • 　　评价递归术语，为递归自我参照替换当前工作表内容。 用UNION(并不是UNION ALL)，去除重复的行和与以前 结果行重复的行。 包括所有在递归查询结果中剩余的行，并将它们放入一个临时的中间表。
    
    
  • 　　以中间表的内容替换工作表的内容，然后清空中间表。
    
    
  
  

　　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