在PostgreSQL中,WITH查询提供了一种编写辅助语句以用于较大查询的方法,它有助于将复杂的大型查询分解为更易于阅读的更简单形式。
WITH查询是CTE查询,在多次执行子查询时特别有用,代替临时表同样有用,它只计算一次聚合,并允许无涯教程在查询中按其名称引用它。
在查询中使用它之前,必须先定义WITH子句。
With - 语法
WITH查询的基本语法如下-
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]
其中 name_for_summary_data 是赋予WITH子句的名称。 name_for_summary_data可以与现有表名相同,并且优先。
您可以在WITH中使用数据修改语句(INSERT,UPDATE或DELETE),这使您可以在同一查询中执行几种不同的操作。
With - 递归
递归WITH或分层查询是CTE的一种形式,CTE可以引用其自身,即WITH查询可以引用其自身的输出,因此称为递归。With - 示例
考虑表 COMPANY 具有以下记录:
testdb# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Learnfk | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
现在,让无涯教程使用WITH子句编写查询以从上表中选择记录,如下所示-
With CTE AS (Select ID , NAME , AGE , ADDRESS , SALARY FROM COMPANY ) Select * From CTE;
上面给出的PostgreSQL语句将产生以下输出-
id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Learnfk | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
现在,让无涯教程使用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;
上面给出的PostgreSQL语句将产生以下输出-
sum ------- 25000 (1 row)
让无涯教程使用数据修改语句以及WITH子句编写查询,如下所示。
首先,创建与表COMPANY类似的表COMPANY1。示例中的查询有效地移动行从公司公司1。 WITH中的DELETE从COMPANY删除指定的行,并通过其RETURNING子句返回其内容,然后主查询读取该输出并将其插入到COMPANY1 TABLE-
CREATE TABLE COMPANY1( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); WITH moved_rows AS ( DELETE FROM COMPANY WHERE SALARY >= 30000 RETURNING * ) INSERT INTO COMPANY1 (SELECT * FROM moved_rows);
上面给出的PostgreSQL语句将产生以下输出-
INSERT 0 3
现在,表COMPANY和COMPANY1中的记录如下-
testdb=# SELECT * FROM COMPANY; id | name | age | address | salary ----+-------+-----+------------+-------- 1 | Paul | 32 | California | 20000 2 | Learnfk | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 7 | James | 24 | Houston | 10000 (4 rows) testdb=# SELECT * FROM COMPANY1; id | name | age | address | salary ----+-------+-----+-------------+-------- 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 (3 rows)
参考链接
https://www.learnfk.com/postgresql/postgresql-with-clause.html
标签:教程,PostgreSQL,name,COMPANY,子句,无涯,查询,20000,SELECT From: https://blog.51cto.com/u_14033984/8956744