HOWTO · PostgreSQL
在一個 PostgreSQL 查詢中使用多個 WITH 語句
在本教程中，我們將學習如何使用多個 WITH 語句在 PostgreSQL 中使用兩個臨時表執行查詢。
本頁內容
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)
