https://blog.csdn.net/chuan_day/article/details/44809125
PostgreSql中WITH语句的使用
With语句是为庞大的查询语句提供了辅助的功能。这些语句通常是引用了表表达式或者CTEs(一种临时数据的存储方式)，可以看做是一个查询语句的临时表。在With语句中可以使用select，insert，update，delete语句。当然with也可以看成是一个单独的语句。
1. With语句中使用select
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语句的常用的方式和场景。当一个查询的结果希望被重用或者这个结果集在另外一个查询中作为数据源，查询条件等使用with语句是比较方便的。当然大部分的with语句是可以被表连接等其他的方式所替代的，只是with语句比较方便，逻辑也很清晰。
比如上面的语句：
a) 首先能从orders表中按照一定的需求查询结果，这里是按照region累加了amount值，然后将结果集放置在regional_sales中。
b) 然后在top_regions临时表中查询regional_sales以作为数据源来进行查询其他要求的结果集。
c) 在with语句外面继续查询orders表并且使用with语句中查询的结果。
d) 总之，使用with语句就是可以将复杂的逻辑通过临时表分布式的查询最终得到理想中的结果。
2．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;
其中RECURSIVE是递归的关键字，一定得有这个标识，PostgreSql才知道这个with语句是做递归操作。
需要说明的是union all的选择可以使union，与一般使用union和union all一样前者是去重的，后者是包含重复数据的。在整个with语句的外面访问临时的表t才能得出整个语句的结果集。
下面介绍相对复杂的递归语句：
with RECURSIVE temp as(
select * from emp where empno = 7698
union all
select b.* from temp a,emp b where b.mgr = a.empno
)select * from temp;
其实结构也是相对比较简单的，掌握这种结构就可以自己扩展，但是需要注意几点：
a) 需要符合union all的语法，即前后两句sql语句的字段要匹配，包括字段名称、字段类型，字段数量要一致；
b) 在with语句外部访问时，temp表可以再与别的表或者视图等关联，甚至继续做递归；
c) 关键点是在select b.* from temp a,emp b where b.mgr = a.empno中，
查询的字段必须由物理存在的表获得,否则会报错，严重时可能宕机。如果需要使用temp中的数据那么需要通过别的方式来访问，比如：
with RECURSIVE temp as(
select ename,empno::text from emp where empno = 7698
union all
select b.ename,b.empno::text||a.empno::text as empno from temp a,emp b where b.mgr::text = a.empno
)select * from temp;
这点必须注意。
3．在with语句中对数据的操作
简言之就是在with语句中进行数据的insert，update，delete操作。基本的结构是：
With temp as (
Delete from table_name where sub_clause
Returning *
)select * from temp
这里的returning是不能缺的，缺了不但返回不了数据还会报错：
错误: WITH 查询 "temp" 没有RETURNING子句
LINE 1: ...as( delete from emp2 where empno = 7369 )select * from temp;
其中“*”表示所有字段，如果只需要返回部分字段那么可以指定字段。
一般应用的场景：
a) 当在一个表中插入数据后，在另外一个表中也要插入之前表插入的数据的主键或者序列。
with temp as(
insert into emp
select * from emp2 where empno = 7369
returning empno
)insert into emp3(empno)
select * from temp;
b) 当update一个表时，需要将update的信息插入到另外一个表中。
with temp as(
update emp a set ename = (select * from emp2 where empno = a.ename) where empno = 7369
returning *
)insert into emp3(ename)
select * from temp;
c) 当删除一个表的部分数据，然后保存这部分删除的数据插入到一个备份表中。
with temp as(
delete from emp where empno = 7369
returning *
)insert into emp3
select * from temp;
2017.12.07 postgresql使用with recursive完成迭代查询
1.表结构 2.需求 查询某条记录的所有父亲节点，或者所有孩子节点。 3.向上查询（查询所有父亲节点） 注意，这里返回的记录包含自己。 sql如下： 1 WITH R...
Teradata 中WITH RECURSIVE 应用的一个实例 - CSDN博客
6-5
需求:对一个位数固定使用分隔符连接的字符串的拆分,如将记录215|482|850|870拆分为215,482,850,870四条记录结果:WITH RECURSIVE 语句能够实现此功能,测试如...
7-2
创建和使用 CTE 的指南下列指南应用于非递归 CTE。有关适用于递归 CTE 的指南,请参阅后面的“定义和使用递归 CTE 的指南”。CTE 之后必须跟随引用部分或全部 ...
Postgresql中临时表（temporary table）的特性和用法
2017.12.07 postgresql使用with recursive完成迭代查询 - CSDN博客
8-16
8-4
PostgreSQL的递归查询(with recursive)
7-24
8-21
定义函数1：sum(1) = 1 定义函数2：sum(n) = n + sum(n - 1) 假设n = 5，那么sum(5) = 5 + sum(4) =5 + 4 + sum(3) =5...
6-23
8-6
WITH语句通常被称为通用表表达式(Common Table Expressions)或者CTEs。 WITH提供了一种方式来书写在一个大型查询中使用的辅助语句。 这些语句通常被称为公共表表达式...
1.SQL的select语句的定义和执行顺序一个完成的SELECT语句包含可选的几个子句。SELECT语句的定义如下： [] [] [] [] [] [] （1）SELECT子句是必选的，其...
6-21
PostgreSQL提供的一种方法，用于写一个大的查询中使用的辅助报表与查询，能使复杂和大型查询简单易读。
1：with子句的使用
使用现有数据库查询，查询在2017.05.01号0点到2017.05.08号0点之间的充值玩家的id,sid,期间充值总数num。
命令如下：
with u as (select id, sum(amount) as num from pay where pay_time >= 1493568000 and pay_time < 1494172800 group by id) select u.id, pinfo.sid, u.num from u join pinfo on u.id=pinfo.id;
解析：
使用with查询出相关玩家的id和期间充值总数num，
再查询出对应id玩家的sid。
2：多个with子句的使用
使用现有数据查询，在1的基础上，增加查询这些玩家到
2017.05.08号0点的历史充值总数total。
with u1 as (select id, sum(amount) as num from pay where pay_time >= 1493568000 and pay_time < 1494172800 group by id), u2 as(select id, sum(amount) as total from pay where pay_time < 1494172800 group by id) select u1.id, pinfo.sid, u1.num, u2.total from u1, u2, pinfo where u2.id = u1.id and pinfo.id = u1.id;
解析：
使用with查询出固定时间内充值的玩家id和期间充值总数，
使用with查询出指定时间之前充值的玩家的id和历史充值总数，
再查询出u1中所有id对应的u2中的数据，以及pinfo中的sid。
9000
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
