介绍PostgreSQL CTE(common table expressions)_数据库

介绍PostgreSQL CTE(common table expressions)

本文我们学习如何使用PostgreSQL CTE(common table expressions)简化复杂查询。

介绍 common table expressions

PostgreSQL CTE(common table expressions) 是临时结果，可以在其他SQL中引用，如SELECT, INSERT, UPDATE 和 DELETE，其仅存在于查询执行期间。下面显示创建CTE的语法：

WITH cte_name (column_list) AS ( 
    CTE_query_definition  
) 
statement;

首先，指定CTE的名称，接着是可选的列字段列表
其次，在with子句体内，指定查询作为返回结果集，如果没有显示指定列字段列表，则CTE_query_definition 的select 字段列表将作为CTE的字段列表。
第三，在其他SQL语句中可以向使用表或视图一样使用CTE，语句包括SELECT, INSERT, UPDATE 和 DELETE。

CTE一般用于简化复杂join和子查询。

示例

下面通过示例更好说明CTE用法。我们使用sample 示例数据库中的 film 和 rental 表。

请看示例：

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 
) 
 
SELECT film_id, title,length 
FROM cte_film 
WHERE length = 'Long' 
ORDER BY  title;

我们首先使用with子句定义CTE，命名为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的名称，这里是cte_film.
第二部分定义一个select 语句用于填充表达式.

接着我们在select中使用cte_film，用于返回length 值为 ‘Long’的记录.

join子句中使用CTE

在下面示例中，我们使用rental和staff表，下面语句说明如何join 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,其staff id 和租借数量
然后，使用 staff_id join staff表和 CTE

和窗口函数一起使用CTE

下面语句描述如何和rank()窗口函数一起使用CTE：

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，返回film信息及每个等级根据长度排名信息，别名为length_rank
其次，选择length_rank为1的film

总结

使用CTE有以下优点：

提升复杂查询的可读性。我们可以使用CTE以更加可读的方式组织复杂查询
能够创建递归查询。递归查询是引用自身，当需要查询层次数据如组织或物料清单信息时，使用递归查询很方便。
和窗口函数一起使用。通过和窗口函数一起创建初始结果集，然后使用select对结果进一步进行处理。

本文参考链接：https://blog.csdn.net/neweastsun/article/details/89608698