PostgreSQL-11-WITH语句
1、WITH语句原理:通用表表达式(Common Table Expressions)/CTEs,在大型查询中的辅助语句
WITH
name_for_summary_data AS (
SELECT Statement)
SELECT columns
FROM name_for_summary_data
WHERE conditions <=> (
SELECT column
FROM name_for_summary_data)
[ORDER BY columns]
CREATE TABLE w1(id int PRIMARY KEY, companyname text, city text, client int);
INSERT INTO w1 VALUES
(1,'c1','shanghai',2000),(2,'c1','beijing',1500),(3,'c1','shenzhen',1000),
(4,'c1','guangzhou',1000),(5,'c2','shanghai',1500),(6,'c2','beijing',3000),
(7,'c2','shenzhen',1000),(8,'c3','shanghai',1000),(9,'c3','beijing',1000),
(10,'c3','shenzhen',4000);
创建表格
WITH t AS (SELECT companyname,city,client FROM w1 WHERE companyname = 'c1')
SELECT SUM(client) FROM t;
先筛选出companyname为c1的临时表t,再对t进行检索(这里t并不存在,\d w2不返回内容)
等价于 → SELECT SUM(client) FROM w1 WHERE companyname = 'c1';
WITH t AS (SELECT companyname,client FROM w1 WHERE city = 'shanghai')
SELECT SUM(client) FROM t;
查询出上海市中c1,c2,c3三个公司的总客户数量
2、按条件移动数据
CREATE TAbLE w2(id int PRIMARY KEY, companyname text, city text, client int); 创建空表w2
WITH t AS (
DELETE FROM w1 WHERE client < 2000 条件,注意这里会修改w1
RETURNING * ) RETURNING子句:返回数据
INSERT INTO w2(SELECT * FROM t); 将数据插入w2表格
按照条件将w1的数据移动到w2中
注意区别 → CREATE TABLE w2 AS SELECT * FROM w1 WHERE client < 2000; 这里不会改变w1数据
最新文章
- linux install mysql
- SQL 2012 Group By Rollup, Grouping
- 配置不当导致无法加载odoo-10.0模块
- MongoDB学习笔记六:进阶指南
- 没有Google的日子很难过...But you can try...
- Merge Two Sorted Lists
- Java学习-003-JDK、JRE、JVM简介
- Python进阶06 循环对象
- 【转】android camera(一):camera模组CMM介绍
- AngularJS的初始化
- SQL Server中关于基数估计如何计算预估行数的一些探讨
- 201521123010 《Java程序设计》第5周学习总结
- Linux系统把/home重新挂载到其他硬盘或分区
- DS1-13
- git Remote: HTTP Basic: Access denied Git failed with a fatal error.
- Python三十个常见的脚本汇总
- python基础(11)-常用模块
- 12. The Biggest Safety Threat Facing Airlines 航空公司面临的最大安全威胁
- ADI SHARC 学习之PLL
- C# 将一个string数组转换为int数组