Postgresql CTE解析
一、简介
WITH提供了一种方式来书写在一个大型查询中使用的辅助语句。这些语句通常被称为公共表表达式或CTE(Common Table Expressions),它们可以被看成是定义只在一个查询中存在的临时表。在WITH子句中的每一个辅助语句可以是一个SELECT、INSERT、UPDATE或DELETE,并且WITH子句本身也可以被附加到一个主语句,主语句也可以是SELECT、INSERT、UPDATE或DELETE。在 PostgreSQL 中,WITH 子句提供了一种编写辅助语句的方法,以便在更大的查询中使用。
二、CTE
CTE一般用于简化复杂join和子查询。WITH
中SELECT
的基本价值是将复杂的查询分解称为简单的部分
三、WITH RECURSIVE(WITH 递归)
WITH语句还可以通过增加RECURSIVE
修饰符来引入它自己,从而实现递归。在 WITH 子句中可以使用自身输出的数据。公用表表达式 (CTE) 具有一个重要的优点,那就是能够引用其自身,从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。
WITH RECURSIVE实例:
从上面的例子可以看出,WITH RECURSIVE语句包含了两个部分 non-recursive term(非递归部分),即上例中的union all前面部分 recursive term(递归部分),即上例中union all后面部分
执行步骤如下 :
1、执行non-recursive term。(如果使用的是union而非union all,则需对结果去重)其结果作为recursive term中对result的引用,同时将这部分结果放入临时的working table中
2、重复执行如下步骤,直到working table为空:用working table的内容替换递归的自引用,执行recursive term,(如果使用union而非union all,去除重复数据),并用该结果(如果使用union而非union all,则是去重后的结果)替换working table 以上面的query为例,来看看具体过程。
以上面的query为例,来看看具体过程:
四、示例
1、创建 COMPANY 表,数据内容如下
2、下面将使用 WITH 子句在上表中查询数据:
3、接下来让我们使用 RECURSIVE 关键字和 WITH 子句编写一个查询,查找 SALARY(工资) 字段小于 20000 的数据并计算它们的和:
4、下面我们建立一张和 COMPANY 表相似的 COMPANY1 表,使用 DELETE 语句和 WITH 子句删除 COMPANY 表中 SALARY(工资) 字段大于等于 30000 的数据,并将删除的数据插入 COMPANY1 表,实现将 COMPANY 表数据转移到 COMPANY1 表中:
5、CAMPANY 表和 CAMPANY1 表的数据如下:
五、总结
1、可以使用递归 WITH RECURSIVE,从而实现其它方式无法实现或者不容易实现的查询
2、当不需要将查询结果被其它独立查询共享时,它比视图更灵活也更轻量
3、CTE只会被计算一次,且可在主查询中多次使用
4、CTE可极大提高代码可读性及可维护性
5、CTE不支持将主查询中where后的限制条件push down到CTE中,而普通的子查询支持
最新文章
- mvn-打jar运行包(含环境变量配置)
- Atitit 边缘检测原理attilax总结
- 嵌入式Linux驱动学习之路(十)字符设备驱动-my_led
- HDU5892~HDU5901 2016网络赛沈阳
- Maven3路程(四)用Maven创建Struts2项目
- scala实现kmeans算法
- Careercup - Facebook面试题 - 6299074475065344
- Cocos2d-x中自定义粒子系统
- C++ 字符串各种处理
- Java笔记(二十)……线程间通信
- 计算1到n整数中,字符ch出现的次数
- (Relax 数论1.8)POJ 1284 Primitive Roots(欧拉函数的应用: 以n为模的本原根的个数phi(n-1))
- 512M内存机器如何用好Mysql
- QMessageBox 中的 OK 按钮改为中文“确定”
- HashMap二三事
- 第60节:Java中的JavaScript技术
- txt文本处理---行未添加逗号
- MongoDB之$
- BZOJ3531[Sdoi2014]旅行——树链剖分+线段树
- Java 8 新特性-菜鸟教程 (5) -Java 8 Stream
热门文章
- 【Shell脚本案例】案例6:查看网卡实时流量
- python3 利用当前时间、随机数产生一个唯一的数字作为文件名
- 常用的渗透测试工具——SQLMap安装
- Spark详解(07-1) - SparkStreaming案例实操
- Vue + Element 自定义上传封面组件
- Java环境搭载的低级错误
- 腾讯出品小程序自动化测试框架【Minium】系列(一)环境搭建之第一个测试程序
- Java基础学习笔记-类与对象
- flutter报错The type of the function literal can't be inferred because the literal has a block as its body.A value of type 'String?' can't be assigned to a variable of type 'String'.
- 高并发环境下3种方式优化Tomcat性能