PostgreSql中WITH语句的使用

2024-09-08 00:58
文章标签 使用 postgresql 语句

本文主要是介绍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递归查询实现树状结构查询

kongxx

 1.5万

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

PostgreSQL 之 with查询

pg_hgdb

 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）的特性和用法

LHDZ_BJ

 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)

wenzhihui_2010

 6784

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

关于SQL语句中With的用法

Strawberry0924

 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)：

qq1712088151

 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语句的用法

heqiyu34

 1173

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

MySQL问题集锦

K346K346

 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语句

huangwenyi1010

 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语句的使用的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1146663。

相关文章

springboot security快速使用示例详解

springboot security快速使用示例详解

《springbootsecurity快速使用示例详解》：本文主要介绍springbootsecurity快速使用示例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝... 目录创www.chinasem.cn建spring boot项目生成脚手架配置依赖接口示例代码项目结构启用s
阅读更多...
Python如何使用__slots__实现节省内存和性能优化

Python如何使用__slots__实现节省内存和性能优化

《Python如何使用__slots__实现节省内存和性能优化》你有想过,一个小小的__slots__能让你的Python类内存消耗直接减半吗,没错,今天咱们要聊的就是这个让人眼前一亮的技巧,感兴趣的... 目录背景：内存吃得满满的类__slots__：你的内存管理小助手举个大概的例子：看看效果如何？1.
阅读更多...
java中使用POI生成Excel并导出过程

java中使用POI生成Excel并导出过程

《java中使用POI生成Excel并导出过程》：本文主要介绍java中使用POI生成Excel并导出过程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录需求说明及实现方式需求完成通用代码版本1版本2结果展示type参数为atype参数为b总结注：本文章中代码均为
阅读更多...
Spring Boot3虚拟线程的使用步骤详解

Spring Boot3虚拟线程的使用步骤详解

《SpringBoot3虚拟线程的使用步骤详解》虚拟线程是Java19中引入的一个新特性,旨在通过简化线程管理来提升应用程序的并发性能,：本文主要介绍SpringBoot3虚拟线程的使用步骤,... 目录问题根源分析解决方案验证验证实验实验1：未启用keep-alive实验2：启用keep-alive扩展建
阅读更多...
MySQL INSERT语句实现当记录不存在时插入的几种方法

MySQL INSERT语句实现当记录不存在时插入的几种方法

《MySQLINSERT语句实现当记录不存在时插入的几种方法》MySQL的INSERT语句是用于向数据库表中插入新记录的关键命令,下面：本文主要介绍MySQLINSERT语句实现当记录不存在时... 目录使用 INSERT IGNORE使用 ON DUPLICATE KEY UPDATE使用 REPLACE
阅读更多...
使用Java实现通用树形结构构建工具类

使用Java实现通用树形结构构建工具类

《使用Java实现通用树形结构构建工具类》这篇文章主要为大家详细介绍了如何使用Java实现通用树形结构构建工具类,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 目录完整代码一、设计思想与核心功能二、核心实现原理1. 数据结构准备阶段2. 循环依赖检测算法3. 树形结构构建4. 搜索子
阅读更多...
GORM中Model和Table的区别及使用

GORM中Model和Table的区别及使用

《GORM中Model和Table的区别及使用》Model和Table是两种与数据库表交互的核心方法,但它们的用途和行为存在著差异,本文主要介绍了GORM中Model和Table的区别及使用,具有一... 目录1. Model 的作用与特点1.1 核心用途1.2 行为特点1.3 示例China编程代码2. Tab
阅读更多...
SpringBoot使用OkHttp完成高效网络请求详解

SpringBoot使用OkHttp完成高效网络请求详解

《SpringBoot使用OkHttp完成高效网络请求详解》OkHttp是一个高效的HTTP客户端,支持同步和异步请求,且具备自动处理cookie、缓存和连接池等高级功能,下面我们来看看SpringB... 目录一、OkHttp 简介二、在 Spring Boot 中集成 OkHttp三、封装 OkHttp
阅读更多...
使用Python实现获取网页指定内容

使用Python实现获取网页指定内容

《使用Python实现获取网页指定内容》在当今互联网时代,网页数据抓取是一项非常重要的技能,本文将带你从零开始学习如何使用Python获取网页中的指定内容,希望对大家有所帮助... 目录引言1. 网页抓取的基本概念2. python中的网页抓取库3. 安装必要的库4. 发送HTTP请求并获取网页内容5. 解
阅读更多...
使用Python实现网络设备配置备份与恢复

使用Python实现网络设备配置备份与恢复

《使用Python实现网络设备配置备份与恢复》网络设备配置备份与恢复在网络安全管理中起着至关重要的作用,本文为大家介绍了如何通过Python实现网络设备配置备份与恢复,需要的可以参考下... 目录一、网络设备配置备份与恢复的概念与重要性二、网络设备配置备份与恢复的分类三、python网络设备配置备份与恢复实
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2