PostgreSQL with（with recursive ）查询

---

前言

WITH提供了一种方式来书写在一个大型查询中使用的辅助语句，可以把他看做成一张临时表。
WITH语句作为一个辅助语句依附于主语句，也叫CTE。WITH语句和主语句都可以是SELECT，INSERT，UPDATE，DELETE中的任何一种语句。

---

一、with查询

with 查询参考
WITH中SELECT的基本价值是将复杂的查询分解称为简单的部分。
with as 这种查询中建立的表为临时表 查询的时候存在 不查询的时候就取消
常见的格式为

with 临时表名 as  （），临时表名2 as()   select 查询语句 

二、with 修改

• WITH中可以不仅可以使用SELECT语句，同时还能使用DELETE，UPDATE，INSERT语句。因此，可以使用WITH，在一条SQL语句中进行不同的操作，如下例所示。

WITH eResult AS (
	UPDATE users SET score = 11 WHERE player = '阿杜'
	RETURNING *
)
INSERT INTO users_log SELECT * FROM eResult; 

本例的WITH子句修改了users表的一条数据，并通过RETURNING * 将修改的这条记录赋给eResult这一CTE，最后在主语句中通过INSERT将修改的用户信息插入users_log中。

• 如果WITH里面使用的不是SELECT语句，并且没有通过RETURNING子句返回结果集，则主查询中不可以引用该CTE，但主查询和WITH语句仍然可以继续执行。这种情况可以实现将多个不相关的语句放在一个SQL语句里，实现了在不显式使用事务的情况下保证WITH语句和主语句的事务性，如下例所示。
• WITH中的子语句被和每一个其他子语句以及主查询并发执行。因此在使用WITH中的数据修改语句时，指定更新的顺序实际是以不可预测的方式发生的。RETURNING数据是在不同WITH子语句和主查询之间传达改变的唯一方法。

WITH eResult AS (
	UPDATE users SET score = 12 WHERE player = '阿杜'
)
SELECT * FROM users;

• WITH中的子语句被和每一个其他子语句以及主查询并发执行。因此在使用WITH中的数据修改语句时，指定更新的顺序实际是以不可预测的方式发生的。RETURNING数据是在不同WITH子语句和主查询之间传达改变的唯一方法。

这样查询出来的是修改前的数据

WITH eResult AS (
	UPDATE users SET score = 12 WHERE player = '阿杜'
)
SELECT * FROM users;

这样查询出来的是修改后的数据

WITH eResult AS (
	UPDATE users SET score = 12 WHERE player = '阿杜'
)
SELECT * FROM eResult ;

with 查询的注意事项
1、WITH中的数据修改语句会被执行一次，并且肯定会完全执行，无论主语句是否读取或者是否读取所有其输出。而WITH中的SELECT语句则只输出主语句中所需要记录数。
2、WITH中使用多个子句时，这些子句和主语句会并行执行，所以当存在多个修改子语句修改相同的记录时，它们的结果不可预测。
3、所有的子句所能“看”到的数据集是一样的，所以它们看不到其它语句对目标数据集的影响。这也缓解了多子句执行顺序的不可预测性造成的影响。
4、如果在一条SQL语句中，更新同一记录多次，只有其中一条会生效，并且很难预测哪一个会生效。
5、如果在一条SQL语句中，同时更新和删除某条记录，则只有更新会生效。
6、目前，任何一个被数据修改CTE的表，不允许使用条件规则，和ALSO规则以及INSTEAD规则。

with查询的总结
1、 可以使用递归 WITH RECURSIVE，从而实现其它方式无法实现或者不容易实现的查询
2、 当不需要将查询结果被其它独立查询共享时，它比视图更灵活也更轻量
3、 CTE只会被计算一次，且可在主查询中多次使用
4、 CTE可极大提高代码可读性及可维护性
5、 CTE不支持将主查询中where后的限制条件push down到CTE中，而普通的子查询支持

三、with recursive

可选的RECURSIVE修饰符将WITH从单纯的句法便利变成了一种在标准SQL中不能完成的特 性。通过使用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;

查询结果为 5050

利用WITH RECURSIVE进行递归查找层级，例如查询类别ALL下的所有子类别（同样也可以查询所有父类别）

--查询层级 
WITH RECURSIVE pct AS(
	SELECT pca.ID, CAST(pca.name AS TEXT) FROM product_category pca WHERE pca."id" = 1
	UNION ALL
	SELECT pck.id, CAST(t.name || '>' || pck.name AS TEXT) FROM product_category pck INNER JOIN pct t ON t.id =     pck.parent_id
)SELECT ID, NAME FROM pct;

±—±---------------------+
| id | name |
±—±---------------------+
| 1 | All |
| 2 | All>Saleable |
| 3 | All>Expenses |
| 4 | All>药品 |
| 5 | All>药品>中药 |
| 6 | All>药品>西药 |
| 7 | All>药品>中药>中成药 |
±—±---------------------+
7 rows in set

官方解释

一个递归WITH查询的通常形式总是一个非递归项，然后是UNION（或者UNION ALL），再然 后是一个递归项，其中只有递归项能够包含对于查询自身输出的引用。这样一个查询可以被 这样执行：

递归查询求值

1计算非递归项。对UNION（但不对UNION ALL），抛弃重复行。把所有剩余的行包括在 递归查询的结果中，并且也把它们放在一个临时的工作表中。
2只要工作表不为空，重复下列步骤：
a. 计算递归项，用当前工作表的内容替换递归自引用。对UNION（不是UNION
ALL），抛弃重复行以及那些与之前结果行重复的行。将剩下的所有行包括在递 归查询的结果中，并且也把它们放在一个临时的中间表中。
b. 用中间表的内容替换工作表的内容，然后清空中间表。
注意: 严格来说，这个处理是迭代而不是递归，但是RECURSIVE是SQL标准委员会选择的术语。