PostgreSQL WITH子句

最新推荐文章于 2024-09-19 07:01:51 发布

DemonHunter211

最新推荐文章于 2024-09-19 07:01:51 发布

阅读量264

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/kwame211/article/details/84587107

版权

数据库专栏收录该内容

145 篇文章 3 订阅

订阅专栏

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

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

WITH子句必须定义，在能在查询中使用。 www.yiibai.com

语法:

在with查询的基本语法如下：

<span style="color:#000000">WITH
   name_for_summary_data AS <span style="color:#666600">(</span>
     SELECT <span style="color:#7f0055">Statement</span><span style="color:#666600">)</span>
   SELECT columns
   FROM name_for_summary_data
   WHERE conditions <span style="color:#666600"><=></span> <span style="color:#666600">(</span>
                      SELECT column
                      FROM name_for_summary_data<span style="color:#666600">)</span>
   <span style="color:#666600">[</span>ORDER BY columns<span style="color:#666600">]</span>  </span>

其中name_for_summary_data是指定的名称WITH子句。name_for_summary_data现有的表名可以是相同的将被优先考虑。

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

递归WITH

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

实例

考虑表COMPANY 有如下记录：

<span style="color:#000000">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)
  </span>

现在，让我们写一个查询使用WITH子句来选择从上面的表中，记录如下：

<span style="color:#000000"><span style="color:#7f0055">With</span> CTE AS
<span style="color:#666600">(</span><span style="color:#7f0055">Select</span>
 ID
<span style="color:#666600">,</span> NAME
<span style="color:#666600">,</span> AGE
<span style="color:#666600">,</span> ADDRESS
<span style="color:#666600">,</span> SALARY
FROM COMPANY <span style="color:#666600">)</span>
<span style="color:#7f0055">Select</span> <span style="color:#666600">*</span> <span style="color:#7f0055">From</span> CTE<span style="color:#666600">;</span> <span style="color:#FFFFFF">www.yiibai.com</span> </span>

以上PostgreSQL的表会产生以下结果： www.yiibai.com

<span style="color:#000000">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)
  </span>

现在，让我们写一个查询使用递归关键字WITH子句，找到小于20000的薪金总和，如下：

<span style="color:#000000">WITH RECURSIVE t<span style="color:#666600">(</span>n<span style="color:#666600">)</span> AS <span style="color:#666600">(</span>
    VALUES <span style="color:#666600">(</span><span style="color:#006666">0</span><span style="color:#666600">)</span>
    UNION ALL
    SELECT SALARY FROM COMPANY WHERE SALARY <span style="color:#666600"><</span> <span style="color:#006666">20000</span>
<span style="color:#666600">)</span>
SELECT sum<span style="color:#666600">(</span>n<span style="color:#666600">)</span> FROM t<span style="color:#666600">;</span> <span style="color:#FFFFFF">yiibai.com</span> </span>

以上PostgreSQL表会产生以下结果：

<span style="color:#000000">  sum
-------
 25000
(1 row)
  </span>

让我们使用数据修改语句，以及WITH子句编写一个查询，如下所示。首先创建一个类似于表公司表COMPANY1。查询在这个例子中，有效地移动行，从COMPANY到COMPANY1。删除删除指定的公司行RETURNING子句返回它们的内容;然后主查询读取输出，并将其插入到COMPANY1 表：

<span style="color:#000000">CREATE TABLE COMPANY1<span style="color:#666600">(</span>
   ID INT PRIMARY KEY     NOT NULL<span style="color:#666600">,</span>
   NAME           TEXT    NOT NULL<span style="color:#666600">,</span>
   AGE            INT     NOT NULL<span style="color:#666600">,</span>
   ADDRESS        CHAR<span style="color:#666600">(</span><span style="color:#006666">50</span><span style="color:#666600">),</span>
   SALARY         REAL
<span style="color:#666600">);</span>

WITH moved_rows AS <span style="color:#666600">(</span>
    DELETE FROM COMPANY
    WHERE
        SALARY <span style="color:#666600">>=</span> <span style="color:#006666">30000</span>
    RETURNING <span style="color:#666600">*</span>
<span style="color:#666600">)</span>
INSERT INTO COMPANY1 <span style="color:#666600">(</span>SELECT <span style="color:#666600">*</span> FROM moved_rows<span style="color:#666600">);</span> <span style="color:#FFFFFF">www.yiibai.com</span> </span>

以上PostgreSQL的表会产生以下结果：

<span style="color:#000000">INSERT 0 3
  </span>

现在的记录表COMPANY与COMPANY1 如下：

<span style="color:#000000">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)  </span>