2023年6月27日发(作者：)

PostgreSQL-WITHAS短语WITH提供了⼀种⽅式来书写在⼀个⼤型查询中使⽤的辅助语句。这些语句通常被称为公共表表达式或CTE，它们可以被看成是定义只在⼀个查询中存在的临时表。在WITH⼦句中的每⼀个辅助语句可以是⼀个SELECT、INSERT、UPDATE或DELETE，并且WITH⼦句本⾝也可以被附加到⼀个主语句，主语句也可以是SELECT、INSERT、UPDATE或DELETE。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_salesFROM ordersWHERE region IN (SELECT region FROM top_regions)GROUP BY region, product;

with语句与全连接WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' UNION ALL SELECT _part, , ty FROM included_parts pr, parts p WHERE = _part)SELECT sub_part, SUM(quantity) as total_quantityFROM included_partsGROUP BY sub_part

创建表、插⼊数据--DROP TABLE COMPANY;CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL);INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (1, 'Paul', 32, 'California', 20000.00 );INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (2, 'Allen', 25, 'Texas', 15000.00 );INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (5, 'David', 27, 'Texas', 85000.00 );INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );

接下来让我们使⽤ RECURSIVE 关键字和 WITH ⼦句编写⼀个查询，查找 SALARY(⼯资) 字段⼩于 20000 的数据并计算它们的和：WITH RECURSIVE t(n) AS ( VALUES (0) UNION ALL SELECT SALARY FROM COMPANY WHERE SALARY < 20000)SELECT sum(n) FROM t; 建⽴⼀张和 COMPANY 表相似的 COMPANY1 表，使⽤ DELETE 语句和 WITH ⼦句删除 COMPANY 表中 SALARY(⼯资) 字段⼤于等于 30000的数据，并将删除的数据插⼊ COMPANY1 表，实现将 COMPANY 表数据转移到 COMPANY1 表中：CREATE TABLE COMPANY1( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL);--删除 COMPANY 表中 SALARY(⼯资) 字段⼤于等于 30000 的数据，WITH moved_rows AS ( DELETE FROM COMPANY WHERE SALARY >= 30000 RETURNING * )--并将删除的数据插⼊ COMPANY1 表INSERT INTO COMPANY1 (SELECT * FROM moved_rows);