PostgreSQL查询中使用递归

WITH查询

官网对WITH的解释：

WITH provides a way to write auxiliary statements for use in a larger query. These statements, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query. Each auxiliary statement in a WITH clause can be a SELECT, INSERT, UPDATE, or DELETE; and the WITH clause itself is attached to a primary statement that can also be a SELECT, INSERT, UPDATE, or DELETE.

WITH提供了一种方式来书写在一个大型查询中使用的辅助语句。这些语句通常被称为公共表表达式或CTE，它们可以被看成是定义只在一个查询中存在的临时表。在WITH子句中的每一个辅助语句可以是一个SELECT、INSERT、UPDATE或DELETE，并且WITH子句本身也可以被附加到一个主语句，主语句也可以是SELECT、INSERT、UPDATE或DELETE。

我们来看一个例子。

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

它只显示在高销售区域每种产品的销售总额。WITH子句定义了两个辅助语句regional_sales和top_regions，其中regional_sales的输出用在top_regions中而top_regions的输出用在主SELECT查询。这个例子可以不用WITH来书写，但是我们必须要用两层嵌套的子SELECT。使用这种方法要更简单些。

递归查询

下面的例子是计算从1到100的整数的总和的查询。

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

我们再来玩一个产生斐波那契数列的例子。

斐波那契数列由0和1开始，之后的斐波那契系数就是由之前的两数相加而得出。

注意”0”是省略掉的，所以数列长这个样子：

(0, ), 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, …

WITH RECURSIVE r ( a, b ) AS ( 
	SELECT 0::INT, 1::INT 
	UNION ALL 
	SELECT b, a + b FROM r 
	WHERE b < 1000 
) 
SELECT b FROM r;

我们可以这样理解，r (a, b) 是一个包含a, b 两列的临时表，后面的语句是为了迭代产生临时表的数据。

我们再来看一个实际项目中的例子。

有一张mes_workcenter表，用来表示MES系统中的工作中心，此处的工作中心是一个宽泛的概念，它可以是工厂，也可以是产线，机器，工位。

工作中心表有三个字段（为了更方便演示省略了其他字段），id, name, parent_id。分别表示工作中心的编号，名称，父工作中心的ID。它的数据示例如下：

id	name	parent_id
1	L2M2集团
2	L2M2成都有限公司	1
3	成都一厂	2
4	包装部	3
5	产线一	4
6	产线二	4

我们要查询L2M2集团下所有的工作中心，以及工作中心的子工作中心，以此类推。

WITH RECURSIVE t(id) AS (
	SELECT id FROM mes_workcenter
	WHERE name = 'L2M2集团'
	UNION ALL
	SELECT B.id FROM t
	INNER JOIN mes_workcenter AS B ON B.parent_id = t.id
)
SELECT * FROM t

PostgreSQL查询中使用递归

PostgreSQL查询中使用递归

WITH查询

递归查询

参考