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

David Mbochi Njonge 2023年1月30日 2022年5月14日
  1. 在一个 PostgreSQL 查询中使用多个 WITH 语句
  2. 在 PostgreSQL 中使用逗号分隔多个 WITH 语句
在一个 PostgreSQL 查询中使用多个 WITH 语句

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)
David Mbochi Njonge avatar David Mbochi Njonge avatar

David is a back end developer with a major in computer science. He loves to solve problems using technology, learning new things, and making new friends. David is currently a technical writer who enjoys making hard concepts easier for other developers to understand and his work has been published on multiple sites.

LinkedIn GitHub