Skip to main content
 首页 » 数据库

介绍PostgreSQL CTE(common table expressions)

2022年07月19日122kevingrace

介绍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
阅读延展