公共表表达式 (CTE)，也称为“WITH 查询”，用于将复杂的查询分解为更简单、更易于管理的部分，从而增强 SQL 代码的可读性。在编写包含复杂子查询的查询时，它非常方便。在更大的查询中使用复杂子查询之前，它会为复杂子查询提供临时名称。
有两种 CTE：普通 CTE 和递归 CTE（本文不涉及递归 CTE）。

构建公共表表达式

CTE 查询使用以下语法编写：

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

person考虑一个名为;的表。

| id | first_name   | last_name   | email                       | gender | country_of_birth | car_make    |
|----|--------------|-------------|-----------------------------|--------|------------------|-------------|
| 1  | Vikki        | Balsillie   | vbalsillie0@mashable.com    | Female | Indonesia       | Nissan      |
| 2  | Lorettalorna | Fetteplace  | lfetteplace2@tripod.com     | Female | United Kingdom  | Plymouth    |
| 3  | Ileana       | Guerin      | iguerin3@unicef.org         | Female | Bulgaria        | null        |
| 4  | Walden       | Milmo       | null                        | Male   | Russia          | Ford        |
| 5  | Quincy       | Bromont     | null                        | Male   | China           | Lexus       |
| 6  | Maria        | Iddon       | middon8@tripadvisor.com     | Female | Philippines     | Land Rover  |
| 7  | Rog          | McArdell    | rmcardell9@list-manage.com  | Male   | Poland          | null        |

源代码（包括创建此表的查询）可以在GitHub上找到。
我们希望找到拥有电子邮件地址的人。我们可以使用 CTE 来执行此操作，如下所示：

-- Create A CTE named 'got_email' to select rows where the email is non-null
-- and select specific columns from the 'got_email' cte
WITH got_email AS (SELECT * FROM person WHERE email IS NOT NULL)
SELECT id,first_name,last_name FROM got_email;

CTE 的第一行定义了名为 的 CTE got_email。personCTE 从表中选择列email非空的所有行。
查询的第二行从 CTE 中选择列的子集got_email。

该查询将返回以下结果：

| id | first_name   | last_name   |
|----|--------------|-------------|
| 1  | Vikki        | Balsillie   |
| 2  | Lorettalorna | Fetteplace  |
| 3  | Ileana       | Guerin      |
| 6  | Maria        | Iddon       |
| 7  | Rog          | McArdell    |

有些关键字不能直接在 CTE 中使用。关键字如 INSERT、UPDATE、DELETE、GROUP BY、HAVING、ORDER BY、LIMIT、OFFSET。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 使用逗号运算符与第二个 CTE 分隔，然后与 CTE 定义外部的 SELECT 语句合并。
多个 CTE 可用于 UNION、UNION ALL、JOIN、INTERSECT 或 EXCEPT 操作。
我们将使用上面的同一张表来解释多个 CTE。表中，有两类记录（人）；同时拥有电子邮件地址和汽车的人，同时拥有电子邮件地址和汽车的人。

| id | first_name   | last_name   | email                       | gender | country_of_birth | car_make    |
|----|--------------|-------------|-----------------------------|--------|------------------|-------------|
| 1  | Vikki        | Balsillie   | vbalsillie0@mashable.com    | Female | Indonesia       | Nissan      |
| 2  | Lorettalorna | Fetteplace  | lfetteplace2@tripod.com     | Female | United Kingdom  | Plymouth    |
| 3  | Ileana       | Guerin      | iguerin3@unicef.org         | Female | Bulgaria        | null        |
| 4  | Walden       | Milmo       | null                        | Male   | Russia          | Ford        |
| 5  | Quincy       | Bromont     | null                        | Male   | China           | Lexus       |
| 6  | Maria        | Iddon       | middon8@tripadvisor.com     | Female | Philippines     | Land Rover  |
| 7  | Rog          | McArdell    | rmcardell9@list-manage.com  | Male   | Poland          | null        |

这次，我们希望过滤表以仅显示该人同时拥有电子邮件地址和汽车的记录。
我们可以使用多个 CTE 来做到这一点：

--Create two CTEs named 'no_email' and 'no_car'
-- filter rows from the 'person' table except those in 'no_email' and 'no_car' CTEs
WITH no_email AS (SELECT * FROM person WHERE email IS NULL),
no_car AS (SELECT * FROM person WHERE car_make IS NULL)
SELECT * FROM person EXCEPT
(SELECT * FROM no_email UNION ALL
SELECT * FROM no_car);

第一行定义 CTE ，它从表中选择列为空的no_email所有记录。由逗号运算符分隔的是第二个 CTE ，它从表中选择列为空的所有记录。 CTE 定义之后是外部查询，它过滤表中的行，排除 CTE 中的行。 结果表；personemailno_carpersoncar_make
SELECTpersonno_emailno_car

| id | first_name   | last_name   | email                       | gender | country_of_birth | car_make    |
|----|--------------|-------------|-----------------------------|--------|------------------|-------------|
| 1  | Vikki        | Balsillie   | vbalsillie0@mashable.com    | Female | Indonesia       | Nissan      |
| 2  | Lorettalorna | Fetteplace  | lfetteplace2@tripod.com     | Female | United Kingdom  | Plymouth    |
| 6  | Maria        | Iddon       | middon8@tripadvisor.com     | Female | Philippines     | Land Rover  |

比较 CTE 和临时表

您可能想知道“嗯，这听起来像临时表，为什么不直接使用临时表呢？”。CTE 和临时表之间存在一些关键区别。

1. CTE 是使用WITH子句定义的。临时表是使用CREATE TEMPORARY TABLE语句创建的。
2. CTE 不作为物理对象存储在数据库中，这意味着它不存储在磁盘上。临时表是临时存在于数据库服务器上的物理表。它消耗磁盘空间。
3. CTE 是一个临时结果集，仅在引用它的单个查询期间存在。临时表在会话期间存在。
4. CTE 并不一定保证运行时性能的提高。但是，它有助于组织您的查询。临时表可以提高运行时性能，特别是当我们需要多次引用相同的数据时。
5. CTE 本身不能直接拥有索引。但是，您可以在 CTE 引用的表上创建索引。您可以在临时表的列上创建索引，就像使用常规表一样。

结论

CTE 可以通过将复杂查询分解为更小的命名片段来使复杂查询更具可读性，从而更容易理解查询的逻辑。它还促进更好的代码组织。
CTE 提供了一种不涉及数据库服务器上的物理存储的解决方案。通过利用 CTE 的优势，开发人员可以编写易于理解的代码，并在需要时进行更好的优化。