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