迹忆客 专注技术分享

with 语句用于创建临时表，这意味着这些表不会持久化在数据库中，并且只存在于内存中，直到查询完成。

引入了 with 语句以将复杂查询分解为更易于处理和调试的简单查询。

本篇文章将教授如何使用多个 with 语句在 PostgreSQL 中使用两个临时表执行查询。

在一个 PostgreSQL 查询中使用多个 WITH 语句

使用以下命令登录到你的 PostgreSQL 数据库。默认用户是 postgres。

如果数据库中有多个用户，请更改用户名。如果你在登录期间配置了用户身份验证，则在下一个提示中输入密码。

david@david-HP-ProBook-6470b:~$ psql -U postgres

成功登录 PostgreSQL 服务器后，使用以下命令创建并连接到我们将用于存储数据的数据库。

postgres=# create database multiple_with_db;
CREATE DATABASE
postgres=# \c multiple_with_db;
You are now connected to database "multiple_with_db" as user "postgres".

我们首先需要创建两个持久表，我们将从中创建临时表。第一个表将保存客户数据。

创建 customer 表，如以下数据定义语言所示。要创建表格，你可以将查询复制并粘贴到终端上，然后按 Enter。

multiple_with_db=# create table customer(customer_id SERIAL UNIQUE NOT NULL,first_name varchar(50),last_name varchar(50),email varchar(60),PRIMARY KEY(customer_id));
CREATE TABLE

使用以下数据操作语言在 customer 表中创建一些客户。你可以在终端上复制并粘贴查询以将记录插入表中。

multiple_with_db=# insert into customer(first_name, last_name, email) values('john','doe','john@gmail.com');
INSERT 0 1
multiple_with_db=# insert into customer(first_name, last_name, email) values('mary','public','mary@gmail.com');
INSERT 0 1
multiple_with_db=# insert into customer(first_name, last_name, email) values('peter','parker','peter@gmail.com');
INSERT 0 1
multiple_with_db=# insert into customer(first_name, last_name, email) values('steve','harvey','steve@gmail.com');
INSERT 0 1

使用以下查询来验证你的记录是否已成功创建。

multiple_with_db=# select * from customer;

输出：

 customer_id | first_name | last_name |      email
-------------+------------+-----------+-----------------
           1 | john       | doe       | john@gmail.com
           2 | mary       | public    | mary@gmail.com
           3 | peter      | parker    | peter@gmail.com
           4 | steve      | harvey    | steve@gmail.com
(4 rows)

第二个表包含客户购买产品的订单信息。创建 customer_order 表，如下所示。

multiple_with_db=# create table customer_order(order_id SERIAL UNIQUE NOT NULL, product_name varchar(50), product_price integer, product_quantity integer, total_price integer, created_at DATE, cust_id integer REFERENCES customer(customer_id));
CREATE TABLE

将一些记录插入 customer_order 表并确保引用完整性约束引用客户，如下所示。

multiple_with_db=# insert into customer_order(product_name, product_price, product_quantity, total_price, created_at, cust_id) values('laptop',500,3,3*500,'2022-03-07',1);
INSERT 0 1
multiple_with_db=# insert into customer_order(product_name, product_price, product_quantity, total_price, created_at, cust_id) values('laptop',500,4,4*500,'2022-03-07',3);
INSERT 0 1
multiple_with_db=# insert into customer_order(product_name, product_price, product_quantity, total_price, created_at, cust_id) values('laptop',500,7,7*500,'2022-03-07',4);
INSERT 0 1
multiple_with_db=# insert into customer_order(product_name, product_price, product_quantity, total_price, created_at, cust_id) values('laptop',500,5,5*500,'2022-03-07',2);
INSERT 0 1

使用以下查询来确保你的数据已成功保存在数据库中。

multiple_with_db=# select * from customer_order;

输出：

 order_id | product_name | product_price | product_quantity | total_price | created_at | cust_id
----------+--------------+---------------+------------------+-------------+------------+---------
        1 | laptop       |           500 |                3 |        1500 | 2022-03-07 |       1
        2 | laptop       |           500 |                4 |        2000 | 2022-03-07 |       3
        3 | laptop       |           500 |                7 |        3500 | 2022-03-07 |       4
        5 | laptop       |           500 |                5 |        2500 | 2022-03-07 |       2
(4 rows)

在 PostgreSQL 中使用逗号分隔多个 WITH 语句

要使用多个 with 语句，第一个 with 语句后跟一个逗号 (,) 而不是另一个 with 语句。

下面的例子展示了我们如何使用多个用逗号分隔的 with 语句来执行查询。

第一个临时表是用 customer 表中的所有数据创建的，第二个临时表是用 customer_order 表中的所有数据创建的。

在临时表上执行查询以返回两列，一列包含客户的电子邮件，另一列包含每个客户购买的产品的总价格。

multiple_with_db=# WITH customer_info AS (select * from customer), order_info AS (select * from customer_order) SELECT (email,total_price) FROM customer_info t1 INNER JOIN order_info t2 ON t1.customer_id=t2.order_id;

输出：

          row
------------------------
 (john@gmail.com,1500)
 (mary@gmail.com,2000)
 (peter@gmail.com,3500)
 (steve@gmail.com,2500)
(4 rows)