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