PostgreSql中WITH语句的使用

本文主要是介绍PostgreSql中WITH语句的使用，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

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...

PostgreSQL递归查询实现树状结构查询

 1.5万

在Postgresql的使用过程中发现了一个很有意思的功能，就是对于需要类似于树状结构的结果可以使用递归查询实现。比如说我们常用的公司部门这种数据结构，一般我们设计表结构的时候都是类似下面的SQL，其...

PostgreSQL 之 with查询

 88

WITH语句通常被称为通用表表达式（Common Table Expressions）或者CTEs。 WITH提供了一种方式来书写在一个大型查询中使用的辅助语句。 这些语句通常被称为公共表表达式或...

Teradata 中WITH RECURSIVE 应用的一个实例 - CSDN博客

6-5

 需求:对一个位数固定使用分隔符连接的字符串的拆分,如将记录215|482|850|870拆分为215,482,850,870四条记录结果:WITH RECURSIVE 语句能够实现此功能,测试如...

关于SQL语句中With的用法 - CSDN博客

7-2

创建和使用 CTE 的指南下列指南应用于非递归 CTE。有关适用于递归 CTE 的指南,请参阅后面的“定义和使用递归 CTE 的指南”。CTE 之后必须跟随引用部分或全部 ...

 

Postgresql中临时表（temporary table）的特性和用法

 5535

熟悉Oracle的人，相比对临时表（temporary table）并不陌生，很多场景下，能很好的解决特定问题。开源库Postgresql中，也有临时表的概念，虽然和Oracle中临时表名字相同，使用...

2017.12.07 postgresql使用with recursive完成迭代查询 - CSDN博客

8-16

1 WITH RECURSIVE res AS ( 2 SELECT t1.* FROM t_code as t1 3 WHERE ...PostgreSql中WITH语句的使用 With语句是为庞大的查询语句提供了辅助的功能。这些语句...

递归(recursive): - CSDN博客

8-4

870拆分为215,482,850,870四条记录结果:WITH RECURSIVE 语句能够实现此功能,...MFC类向导工具class wizard用法介绍 阅读量:2665 表单的action属性和提交按钮 阅读...

PostgreSQL的递归查询(with recursive)

 6784

开发有需求，说需要对一张地区表进行递归查询，Postgres中有个 with recursive的查询方式，可以满足递归查询(一般>=2层)。 测试如下： create table tb(id var...

关于SQL语句中With的用法

 7793

创建和使用 CTE 的指南下列指南应用于非递归 CTE。有关适用于递归 CTE 的指南，请参阅后面的“定义和使用递归 CTE 的指南”。CTE 之后必须跟随引用部分或全部 CTE 列的 SELECT、...

Oracle 11g的递归with语句 - CSDN博客

7-24

关于oracle中with xx as 的用法 u014218318 05-11 708 提高oracle的查询...870拆分为215,482,850,870四条记录结果:WITH RECURSIVE 语句能够实现此功能,...

关于SQL语句中With的用法 - CSDN博客

8-21

关于SQL语句中With的用法2017年02月16日 14:04:28 阅读数:4383 创建...(7, 'Bill', 5, 4); GO -- Create the recursive CTE to find all of...

递归(recursive)：

 186

定义函数1：sum(1) = 1 定义函数2：sum(n) = n + sum(n - 1) 假设n = 5，那么sum(5)  = 5 + sum(4) =5 + 4 + sum(3) =5...

 

with as 用法 - CSDN博客

6-23

其实就是把一大堆重复用到的SQL语句放在with as 里面,取一个别名,后面的查询就...   • Also useful for recursive queries (SQL-99, but not Oracle ...

PostgreSQL 之 with查询 - CSDN博客

8-6

WITH语句通常被称为通用表表达式(Common Table Expressions)或者CTEs。 WITH提供了一种方式来书写在一个大型查询中使用的辅助语句。 这些语句通常被称为公共表表达式...

Oracle with语句的用法

 1173

Oracle with语句是经常可以见到的语句，下面就为您详细介绍Oracle with语句的用法，如果您对Oracle with语句感兴趣的话，不妨一看。 当查询中多次用到某一部分时，可以用Ora...

MySQL问题集锦

 1584

1.SQL的select语句的定义和执行顺序一个完成的SELECT语句包含可选的几个子句。SELECT语句的定义如下： [] [] [] [] [] [] （1）SELECT子句是必选的，其...

ORACLE WITH AS 用法 - CSDN博客

6-21

   • Also useful for recursive queries (SQL-99, but not Oracle ...前言 可以把WITH AS 的用法看成赋值的用法,以减少SQL语句的冗余。 当我们在SQL...

PostgreSQL之With语句

 866

开心一笑妻子：“哎，快到站了，有零钱吗？” 丈夫大惑不解：“你忘性真大。自打和你认识起，我袋里就从来没有过整张的！”提出问题with语句的用法！！！解决问题with语句，可以被看作是定义临时表的存在...

 

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。

这篇关于PostgreSql中WITH语句的使用的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---