PostgreSQL：with recursive使用

先从 with 关键字开始，with 提供了一种编写子查询的方法，这种子查询用于 select 查询语句中。可以将这些子查询(通常被称为 Common Table Expressions 简称 CTE )视为定义了仅用于此查询的临时表。with 的一种用途是将复杂的查询分解成简单的查询语句。例如下面的示例

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;

重点是上面的临时表，基于 orders 表建了两个临时表，整个查询语句都用到了这两个临时表。这点和视图有点类似。with 后面接的就是临时表的名字，可以在后面的 select 语句中用的到。

 with 加入 recursive 关键字后可以实现普通复杂查询。示例如下：

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

执行上面的 sql 会发生什么步骤呢？

# Step 1: initialisation
LET cte_result = EMPTY
LET working_table = VALUES (1)
LET intermediate_table = EMPTY

# Step 2: result initialisation, merge initialisation into cte_result
cte_result = cte_result UNION working_table

# Step 3: iteration test
WHILE (working_table is not empty) DO
　　# Step 4: iteration select, we substitute the self-reference with working_tableintermediate_table = SELECT n+1 FROM working_table WHERE n < 100

　　# Step 5: iteration merge, merge the iteration result into cte_result
　　cte_result = cte_result UNION intermediate_table

　　# Step 6: iteration end, prepare for next iterationworking_table = intermediate_tableintermediate_table = EMPTY
END WHILE

# Step 7: return
RETURN cte_result

上面查询过程中会建立三张表，三张表不断并集，累加，循环，获取最终结果。

简而言之， 以 working_table 为基础表，执行 select 查询语句，将结果赋给 intermediate_table，然后将 intermediate_table 赋给 working_table；以 working_table 为基础表，执行 select 查询语句，如此循环往复，直到 working_table 为 empty，循环停止。在上述循环过程中，每次 intermediate_table 被重新赋值时，就将 intermediate_table 值累加到 cte_result 中，最终的结果集就是 cte_result。

参考：

https://www.postgresql.org/docs/8.4/queries-with.html

https://stackoverflow.com/questions/30015842/how-sql-with-recursive-statement-interpreted