postgresql——WITH语句

前言

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

WITH查询

用户、公司、关联表3表连查

WITH eResult AS (
	SELECT e.id empid,e.name empname FROM emp e
),
ecResult AS (
	SELECT e.*,ec.* FROM eResult e left join ec ON e.empid = ec.eid
),
cResult AS (
	SELECT ec.empid,ec.empname,c.id cid,c.name cname FROM ecResult ec left join company c ON ec.cid = c.id
)
SELECT * FROM cResult;

定义了三条WITH子句，实现了三张表的关联查询。
CTE主要是用来做数据的过滤，然后一层层的查询过滤组装。最终筛选出我们需要的数据，当然你可能会问为什么不一次性拿出所有的数据呢，当然如果数据很大，我们通过多层次的数据过滤组装，在效率上也更好。

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 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规则。

优缺点

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