SQL - 公共表表达式 (CTE)

---

---

公共表表达式 (CTE) 可以通过使复杂查询更易于阅读和简单来简化复杂查询的管理和编写，就像数据库视图和派生表一样。我们可以通过将复杂查询分解成简单的块来重用或重写查询。

SQL 公共表表达式

MySQL 中的 WITH 子句用于指定公共表表达式。

SQL 中的公共表表达式 (CTE) 是一次性结果集，即它是一个仅在单个查询执行期间存在的临时表。它允许我们在该查询中专门处理数据，例如在 SELECT、UPDATE、INSERT、DELETE、CREATE、VIEW 或 MERGE 语句中使用它。

CTE 是临时的，因为它无法存储在任何地方以供以后使用；一旦查询执行完毕，它就会丢失。

MySQL WITH 子句

要指定公共表表达式，我们使用包含一个或多个以逗号分隔的子句的 WITH 子句。在每个子句中，我们可以提供一个产生结果集的子查询，并为该子查询分配一个名称。

在 8.0 之前的 MySQL 版本中，您无法使用 WITH 子句。

语法

以下是使用 WITH 子句创建 CTE 的语法：

WITH CTE_NAME (column_name) AS (query)
SELECT * FROM CTE_NAME;

其中，

• CTE_NAME − 它是在 CTE 中分配给 CTE 的名称。
• column_name − 它是 CTE 的列名，这对于提高查询的可读性很有用。
• query − 它定义 CTE，它可以是任何有效的 SQL 查询。
• 定义 CTE 后，您可以在同一会话中的后续查询中引用它。

示例

假设我们在 MySQL 数据库中使用 CREATE TABLE 语句创建了一个名为 CUSTOMERS 的表，如下所示：

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

现在，我们正在向上面创建的表中插入一些记录：

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

创建的表如下所示：

在这里，我们正在创建一个名为 CUSTOMER_AGE 的公共表表达式 (CTE)，它选择所有年龄为 23 的客户。然后，我们从 CTE 中检索这些客户的 ID、姓名和年龄。

WITH CUSTOMER_AGE AS (
SELECT * FROM customers WHERE AGE = 23)
SELECT ID, NAME, AGE FROM CUSTOMER_AGE;

输出

以下是上述查询的输出：

来自多个表的 CTE

我们还可以创建一个公共表表达式 (CTE)，它通过在 CTE 的子查询中使用 JOIN 操作来组合来自多个表的数据。为此，我们需要使用逗号运算符分隔每个 CTE 定义，有效地将它们合并到一个语句中。

语法

以下是多个公共表表达式 (CTE) 的基本语法：

WITH
   CTE_NAME1 (column_name) AS (query),
   CTE_NAME2 (column_name) AS (query)
SELECT * FROM CTE_NAME1
UNION ALL
SELECT * FROM CTE_NAME2;

我们可以将多个公共表表达式 (CTE) 与各种 SQL 操作一起使用，例如 UNION、UNION ALL、JOIN、INTERSECT 或 EXCEPT。

示例

在这里，我们定义了两个 CTE，即 'CUSTOMERS_IN_DELHI' 和 'CUSTOMERS_IN_MUMBAI'，以根据他们在德里和孟买的地址来区分客户。然后，我们使用 UNION ALL 运算符将来自这两个 CTE 的结果组合到单个结果集中，检索来自这两个城市的客户信息。

WITH
CUSTOMERS_IN_DELHI AS (
   SELECT * FROM CUSTOMERS WHERE ADDRESS = 'Delhi'),
CUSTOMERS_IN_MUMBAI AS (
   SELECT * FROM CUSTOMERS WHERE ADDRESS = 'Mumbai')
SELECT ID, NAME, ADDRESS FROM CUSTOMERS_IN_DELHI
UNION ALL
SELECT ID, NAME, ADDRESS FROM CUSTOMERS_IN_MUMBAI;

输出

上述查询的输出如下所示：

递归 CTE

公共表表达式是一个查询，它会反复循环引用自己的结果，直到返回空结果。

递归查询在其执行期间不断迭代数据的一个子集，并以自引用方式定义自身。这种自引用机制允许它反复处理和扩展其结果，直到满足停止条件。

要使 CTE 成为递归的，它必须包含 UNION ALL 语句并提供查询的第二个定义，该定义利用 CTE 本身。这允许 CTE 反复引用其自身的结果，从而在查询中创建递归行为。

示例

现在，我们使用一个名为 recursive_cust 的递归 CTE 来从上面创建的 'CUSTOMERS' 表中检索数据。最初，我们选择工资超过 3000 的客户，然后使用 UNION ALL 运算符递归地将年龄超过 25 的客户追加到结果集中：

WITH recursive_cust (ID, NAME, ADDRESS, AGE) AS (
   SELECT ID, NAME, ADDRESS, AGE
   FROM CUSTOMERS
   WHERE SALARY > 3000
   UNION ALL
   SELECT ID, NAME, ADDRESS, AGE
   FROM CUSTOMERS
   WHERE AGE > 25
)
SELECT * FROM recursive_cust;

输出

执行上述查询时，将递归显示来自客户表中年龄大于 25 或工资大于 3000 的所有数据，如下所示：

示例

在以下查询中，我们使用一个名为 Numbers 的递归 CTE 来生成并显示从 1 到 5 的数字。递归部分不断将 1 添加到前一个值，直到达到 5，从而创建一个序列：

WITH RECURSIVE Numbers AS (
  SELECT 1 AS N
  UNION ALL
  SELECT N + 1 FROM Numbers WHERE N < 5
)
SELECT n FROM Numbers;

输出

执行上述查询后，我们得到以下输出：

CTE 的优势

以下是 CTE 的优点：

• CTE 使代码维护更容易。

• 它提高了代码的可读性。

• 它提高了查询的性能。

• CTE 允许简单地实现递归查询。

CTE 的劣势

以下是 CTE 的缺点：

• CTE 只能被递归成员引用一次。

• 我们不能在存储过程中使用表变量和 CTE 作为参数。

• CTE 可以代替视图使用，但 CTE 无法嵌套，而视图可以。