在PostgreSQL中如何使用CTE（Common Table Expressions）|极客教程

在PostgreSQL中如何使用CTE（Common Table Expressions）

在PostgreSQL中，通用表表达式（CTE）是一种临时命名的查询结果，可以在查询中被引用多次。CTE提供了一种在查询中创建可重复使用的临时结果集的方法，使得复杂查询更易于编写和阅读。

什么是CTE？

通用表表达式（CTE）是一种SQL查询的结构，允许在查询中创建临时结果集。CTE是通过WITH关键字定义的，并在SELECT、INSERT、UPDATE或DELETE语句中引用。

CTE有两种形式：递归CTE和非递归CTE。递归CTE可以自引用，允许执行递归查询，而非递归CTE只能引用一次。

如何使用CTE

要使用CTE，可以通过WITH关键字定义CTE，然后在SELECT语句中引用它。以下是一个简单的示例：

WITH cte_example AS (
    SELECT * FROM employees WHERE department = 'IT'
)
SELECT * FROM cte_example;

在上面的示例中，我们创建了一个名为cte_example的CTE，它选择了部门为’IT’的员工。然后我们在SELECT语句中引用了这个CTE。

CTE的优势

使用CTE有几个优势：

提高代码的可读性： CTE允许将复杂的查询分解为易于理解的部分，提高代码的可读性和可维护性。
避免重复的子查询： CTE允许在查询中重用相同的子查询，减少冗余代码。
支持递归查询： 递归CTE允许执行递归查询，实现层次结构数据的查询。

示例

非递归CTE

让我们通过一个示例来展示非递归CTE的用法。假设我们有一个名为products的表，包含产品的信息，我们想要查询价格高于平均价格的产品：

WITH high_price_products AS (
    SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products)
)
SELECT * FROM high_price_products;

在上面的示例中，我们使用非递归CTE high_price_products来筛选出价格高于平均价格的产品。

递归CTE

现在让我们看一个递归CTE的示例。假设我们有一个名为categories的表，包含产品类别及其层次结构的信息，我们想要查询某个类别及其所有子类别的信息：

WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE name = 'Electronics'
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

在上面的示例中，我们使用递归CTE category_tree来查询类别为’Electronics’的所有子类别，直到没有子类别为止。