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数据