PHP开发教程

PostgreSQL WITH子句

在PostgreSQL里,提供了一种方法写一个大的查询中使用的辅助报表与查询。它有助于打破复杂和大型查询简单易读的形式。这些声明,这是通常被称为通用表表达式或CTE值,可以被看作是定义临时表的存在只是一个查询。 

WITH查询由CTE查询时特别有用的子查询执行多次。它是代替临时表中同样有帮助。它计算聚合一次,让我们来引用它由它的名字(可能是多次)查询。


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现有的表名可以是相同的将被优先考虑。

可以使用数据修改语句(INSERT,UPDATE或DELETE)与WITH一起。这可以在同一查询中执行不同的操作。


递归WITH

递归或谱系查询,是一种热膨胀系数(CTE)的CTE可以引用本身,即一个具有查询,可以参考自己的输出。因此名称递归。

实例

考虑表COMPANY 有如下记录:


testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  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 | Allen |  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子句,找到小于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子句编写一个查询,如下所示。首先创建一个类似于表公司表COMPANY1。查询在这个例子中,有效地移动行,从COMPANY到COMPANY1。删除删除指定的公司行RETURNING子句返回它们的内容;然后主查询读取输出,并将其插入到COMPANY1 表:


CREATE TABLE COMPANY1(
 ID INT PRIMARY KEY NOT NULL,
 NAME TEXTNOT NULL,
 AGEINT NOT NULL,
 ADDRESSCHAR(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 | Allen |  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)


Powered by PHP二次开发 ©2008-2017