在一個 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