PostgreSQL 教程: WITH 查询 (CTE 公共表表达式)

九月 5, 2023

摘要：在本教程中，您将学习如何使用 PostgreSQL 的 CTE（公共表表达式）来简化复杂的查询。

公共表表达式或 CTE 简介

公共表表达式是一个临时结果集，您可以在另一个 SQL 语句（包括SELECT、INSERT、UPDATE或DELETE）中引用它。

公共表表达式是临时的，因为它们仅在查询执行期间存在。

创建 CTE 的语法如下：

WITH cte_name (column_list) AS (
    CTE_query_definition 
)
statement;

在这个语法中：

首先，指定 CTE 的名称，后跟可选的列列表。
其次，在WITH子句主体内，指定返回结果集的查询。如果您没有在 CTE 名称后显式指定列列表，则CTE_query_definition的查询列表将成为 CTE 的列列表。
第三，在SELECT、INSERT、UPDATE或DELETE等statement中，将 CTE 用作表或视图。

公共表表达式或 CTE 通常用于简化 PostgreSQL 中的复杂连接和子查询。

PostgreSQL CTE 示例

让我们看一些使用 CTE 的示例来更好地理解。

一个简单的 PostgreSQL CTE 示例

我们将使用示例数据库中的film和rental表进行演示。

请参见以下示例：

WITH cte_film AS (
    SELECT 
        film_id, 
        title,
        (CASE 
            WHEN length < 30 THEN 'Short'
            WHEN length < 90 THEN 'Medium'
            ELSE 'Long'
        END) length    
    FROM
        film
)
SELECT
    film_id,
    title,
    length
FROM 
    cte_film
WHERE
    length = 'Long'
ORDER BY 
    title;

这是部分输出：

PostgreSQL CTE Simple Example

请注意，此示例用于演示目的。

在本例中，我们首先使用WITH子句定义了一个名为cte_film的公共表表达式，如下所示：

WITH cte_film AS (
    SELECT 
        film_id, 
        title,
        (CASE 
            WHEN length < 30 
                THEN 'Short'
            WHEN length >= 30 AND length < 90 
                THEN 'Medium'
            WHEN length >=  90 
                THEN 'Long'
        END) length    
    FROM
        film
)

公共表表达式有两部分：

第一部分定义 CTE 的名称，即cte_film。
第二部分定义一个用行填充表达式的SELECT语句。

然后，我们在SELECT语句中使用名为cte_film的 CTE 仅返回长度为 ‘Long’ 的电影。

将 CTE 与表连接的示例

在下面的示例中，我们将使用rental和staff表：

以下语句说明了如何将 CTE 与表连接：

WITH cte_rental AS (
    SELECT staff_id,
        COUNT(rental_id) rental_count
    FROM   rental
    GROUP  BY staff_id
)
SELECT s.staff_id,
    first_name,
    last_name,
    rental_count
FROM staff s
    INNER JOIN cte_rental USING (staff_id);

在这个例子中：

首先，CTE 返回一个结果集，其中包括员工 ID 和租赁数量。
然后，使用staff_id列将staff表与 CTE 连接起来。

这是输出：

将 CTE 与窗口函数结合使用的示例

以下语句说明了如何将 CTE 与RANK() 窗口函数一起使用：

WITH cte_film AS  (
    SELECT film_id,
        title,
        rating,
        length,
        RANK() OVER (
            PARTITION BY rating
            ORDER BY length DESC) 
        length_rank
    FROM 
        film
)
SELECT *
FROM cte_film
WHERE length_rank = 1;

在这个例子中：

首先，我们定义了一个 CTE，它返回每个电影评级按长度排序的电影排名。
其次，我们只选择长度排名为一的电影。

下图显示了输出：

PostgreSQL CTE 优势

以下是使用公共表表达式或 CTE 的一些优点：

提高复杂查询的可读性。您可以使用 CTE 以更有组织性和可读性的方式组织复杂的查询。
能够创建递归查询。递归查询是引用自身的查询。当您想要查询组织结构图或物料清单等分层数据时，递归查询会派上用场。
与窗口函数结合使用。您可以将 CTE 与窗口函数结合使用来创建初始结果集，并使用另一个 select 语句来进一步处理该结果集。

在本教程中，您了解了 PostgreSQL CTE 或公共表表达式来简化复杂查询。