SQL - 公用表表达式 (CTE)

---

通用表表达式 (CTE) 可以使复杂查询（如数据库视图和派生表）变得更具可读性和简单性，从而更轻松地管理和编写复杂查询。我们可以通过将复杂的查询分解为简单的块来重用或重写查询。

SQL 公用表表达式

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

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

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

MySQL的WITH子句

为了指定公用表表达式，我们使用由一个或多个逗号分隔的子句组成的WITH 子句。在每个子条款中，我们可以提供一个子查询，该子查询生成结果集并为该子查询分配一个名称。

MySQL 8.0 之前的版本不能使用WITH 子句。

句法

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

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

在哪里，

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

例子

假设我们使用 CREATE TABLE 语句在 MySQL 数据库中创建了一个名为 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、NAME 和 AGE。

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 重复引用其自己的结果，从而在查询中创建递归Behave。

例子

现在，我们使用名为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;

输出

执行上述查询时，将递归显示customers表中年龄大于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 不能嵌套，而视图可以。