前言

本文介绍如何使用 CTE 表达式来简化 PostgreSQL 中的一些复杂查询。那 CTE 表达式是什么呢?

CTE 介绍

在 PostgreSql 中 WITH 提供了一种方式来书写在一个大型查询中使用的辅助语句。这些语句通常被称为公共表表达式或CTE（Common Table Expressions），它们可以被看成是定义只在一个查询中存在的临时表。在WITH子句中的每一个辅助语句可以是一个SELECT、INSERT、UPDATE或DELETE，并且WITH子句本身也可以被附加到一个主语句，主语句也可以是SELECT、INSERT、UPDATE或DELETE。在 PostgreSQL 中，WITH 子句提供了一种编写辅助语句的方法，以便在复杂的查询中使用。

使用

该如何创建 CTE 呢, 创建 CTE 的语句如下:

1
2
3
4
5
6
7
8

WITH cte_name AS (
    SELECT column1, column2, ...
    FROM table_name
WHERE condition
    ...
    )
SELECT *
FROM cte_name;

在日常查询中 CTE 用于哪些场景呢:

  • 递归查询：CTE 表达式常用于执行递归查询。通过在 CTE 表达式中引用自身，可以简洁地实现递归操作。
  • 复杂查询：CTE 表达式可以用于构建复杂的查询，将查询逻辑分解为更易于理解和维护的部分。每个 CTE 子查询块可以负责不同的逻辑操作，最终组合成一个完整的查询。
  • 数据转换和重组：CTE 表达式可以用于对数据进行转换和重组。通过在不同的 CTE 子查询块中选择、过滤和连接数据，可以生成新的结果集。

递归查询

WITH 表达式如何实现递归查询呢,可以通过添加 RECURSIVE修饰符来实现。下面是一个示例:

1
2
3
4
5
6

WITH RECURSIVE t(n) AS (
    VALUES (1)
    UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

这个例子是对1到100之间数求和。

小结

PostgreSQL 的 CTE 表达式是一种强大的工具，可以帮助我们编写复杂且高效的查询语句。

参考