迹忆客 专注技术分享

在本教程中，我们将学习 MySQL WITH 子句，也称为 Common Table Expression (CTE)。每当你想要操作困难的子查询数据时，都会使用 CTE。

我们还将学习 Common Table Expression (CTE) 如何让你以易于阅读和理解的方式编写复杂的查询。我们还将看看是否可以使用嵌套的 WITH 子句。

请注意，在 MySQL 8.0 版之前，Common Table Expression 不可用。你必须拥有 MySQL 8.0 或更高版本才能使用它。你可以在此处查看 MySQL 8.0 版中的新功能。

使用 MySQL WITH 子句即 Common Table Expression

要使用 MySQL WITH 子句，让我们先了解 CTE。公用表表达式 (CTE) 被命名为临时结果集，仅存在于编写它的语句的执行范围内。

通过使用 WITH 子句，你可以为复杂的子查询指定名称，你可以在主查询中轻松使用该名称（SELECT、INSERT、UPDATE 或 DELETE）。请记住，并非所有数据库都支持 WITH 子句。

你可以在同一个 WITH 子句中使用一个或多个子查询和 CTE，但不能使用嵌套 WITH（WITH 子句中的另一个 WITH）。让我们创建一个名为 tb_order 的 Table 并用一些数据填充它以练习 WITH 子句。

示例代码：

# SQL Programming Using MySQL Version 8.27
CREATE TABLE `practice_with_clause`.`tb_order` (
ORDER_ID INTEGER NOT NULL,
CUSTOMER_FIRST_NAME	VARCHAR(30) NOT NULL,
CUSTOMER_LAST_NAME VARCHAR(30) NOT NULL,
CITY_NAME VARCHAR(64) NOT NULL,
PURCHASED_PRODUCTS VARCHAR(64) NOT NULL,
ORDER_DATE DATE NOT NULL,
PRIMARY KEY (ORDER_ID)
);

确保在 Tables 下的 Database 中成功创建了你的表。

使用下面的 INSERT 命令用 7 条记录填充表。

# SQL Programming Using MySQL Version 8.27
INSERT INTO practice_with_clause.tb_order 
(ORDER_ID, CUSTOMER_FIRST_NAME, CUSTOMER_LAST_NAME, CITY_NAME, PURCHASED_PRODUCTS, ORDER_DATE)
VALUES
(1,'John','Horton', 'Washington', 'Books', '2021-05-03'),
(2,'Banji','Horton',  'Florida', 'Pens', '2010-5-6'),
(3,'Nayya','Sofia',  'South Carolina', 'Books', '2011-10-15'),
(4,'Martell','Daniel',  'Michigan', 'NoteBooks', '2012-12-02'),
(5,'Sana','Preston',  'Michigan', 'White Board Marker', '2013-08-27'),
(6,'Gulraiz','Yonja', 'Washington', 'Books', '2021-05-03'),
(7,'Mashal','Naaz',  'Florida', 'Comic Books', '2019-01-01');

现在，使用 SELECT 命令查看数据。

# SQL Programming Using MySQL Version 8.27
SELECT * FROM practice_with_clause.tb_order;

此时，我们将使用 WITH 子句来使用公共表表达式并操作复杂的子查询，如下所示。

# SQL Programming Using MySQL Version 8.27
WITH cte_order AS 
(
SELECT PURCHASED_PRODUCTS, COUNT(ORDER_ID) as Number_of_Orders
FROM practice_with_clause.tb_order
GROUP BY PURCHASED_PRODUCTS
)
SELECT AVG(Number_of_Orders) AS "Average Orders Per Category"
FROM cte_order;

让我们把上面的查询分解成几个部分来理解：

公用表表达式：cte_order

子查询：

SELECT PURCHASED_PRODUCTS, COUNT(ORDER_ID) as Number_of_Orders FROM practice_with_clause.tb_order GROUP BY PURCHASED_PRODUCTS

主查询：

SELECT AVG(Number_of_Orders) AS "Average Orders Per Category" FROM cte_order;

请注意，CTE 在主查询中引用自身来读取数据。它将根据我的数据显示以下输出（你的输出可能不同）。

公用表表达式执行范围

正如我们所说，CTE 只在其执行范围内工作，如何？请参阅以下屏幕截图。

当你只选择绿色框突出显示的代码时，你仍然在名为 cte_order 的 CTE 的执行范围内，但是当你只选择红色框内的代码时，你现在不在执行范围内，无法引用名为 cte_order 的公用表表达式。这意味着你可以在编写它的同一 WITH 子句中引用 CTE。

使用具有多个公共表表达式的 MySQL WITH 子句

让我们通过使用多个公用表表达式来练习 WITH 子句。

WITH 
cte_order AS 
(
SELECT PURCHASED_PRODUCTS, COUNT(ORDER_ID) as Number_of_Orders
FROM practice_with_clause.tb_order
GROUP BY PURCHASED_PRODUCTS
),
cte_location AS
(
SELECT COUNT(CITY_NAME) as City
FROM practice_with_clause.tb_order
WHERE CITY_NAME = 'Washington'
)
SELECT AVG(Number_of_Orders) AS "Average Orders Per Category", City
FROM cte_order,cte_location;

现在，你将看到以下输出。

类似地，我们也可以引用之前从另一个 CTE 定义的一个 Common Table Expression。确保两个公用表表达式都写在同一个 WITH 子句中。

结论

考虑到上面的讨论，我们得出结论，WITH 子句用于获得公共表表达式的优势，有助于操纵困难的子查询。我们可以在同一个 WITH 子句中使用多个子查询和公用表表达式，但不能有嵌套的 WITH 子句。我们也不能从不同的 WITH 子句中引用 CTE。