PostgreSQL 公共表表达式
公共表表达式（Common Table Expressions，简称 CTE）是 PostgreSQL 中一种强大的工具，用于简化复杂查询并提高代码的可读性。CTE 允许你在一个查询中定义临时结果集，并在后续查询中引用它。这对于分解复杂逻辑或多次引用同一子查询非常有用。
什么是公共表表达式？
CTE 是一个临时的命名结果集，可以在一个 SELECT、INSERT、UPDATE 或 DELETE 语句中使用。它通过 WITH 关键字定义，并在查询中像普通表一样引用。CTE 的主要优势在于它能够将复杂的查询分解为更小、更易管理的部分。
基本语法
WITH cte_name AS (
SELECT ...
)
SELECT * FROM cte_name;
在上面的语法中，cte_name 是 CTE 的名称，括号内的 SELECT 语句定义了 CTE 的内容。定义后，可以在主查询中像普通表一样使用 cte_name。
使用 CTE 的示例
示例 1 ：简单的 CTE
假设我们有一个 employees 表，包含员工的姓名和薪水。我们想找出薪水高于平均薪水的员工。
WITH avg_salary AS (
SELECT AVG(salary) AS avg_sal FROM employees
)
SELECT name, salary
FROM employees, avg_salary
WHERE salary > avg_sal;
在这个例子中，avg_salary CTE 计算了所有员工的平均薪水。然后，主查询从 employees 表中选择薪水高于平均薪水的员工。
示例 2：递归 CTE
CTE 还支持递归查询，这对于处理层次结构数据（如组织结构或树形结构）非常有用。假设我们有一个 departments 表，其中包含部门 ID 和上级部门 ID。
WITH RECURSIVE department_hierarchy AS (
SELECT id, name, parent_id
FROM departments
WHERE parent_id IS NULL
UNION ALL
SELECT d.id, d.name, d.parent_id
FROM departments d
INNER JOIN department_hierarchy dh ON d.parent_id = dh.id
)
SELECT * FROM department_hierarchy;
在这个递归 CTE 中，department_hierarchy 首先选择顶级部门（parent_id 为 NULL），然后递归地选择所有子部门。
实际应用场景
场景 1：数据分页
CTE 可以用于实现数据分页。例如，假设我们想从 orders 表中获取第 11 到第 20 条记录。
WITH ordered_orders AS (
SELECT *
FROM orders
ORDER BY order_date DESC
)
SELECT *
FROM ordered_orders
LIMIT 10 OFFSET 10;
场景 2：复杂的数据转换
CTE 可以用于将复杂的数据转换分解为多个步骤。例如，假设我们有一个 sales 表，我们想计算每个月的总销售额，并找出销售额最高的月份。
WITH monthly_sales AS (
SELECT DATE_TRUNC('month', sale_date) AS month, SUM(amount) AS total_sales
FROM sales
GROUP BY month
),
max_sales AS (
SELECT MAX(total_sales) AS max_sales
FROM monthly_sales
)
SELECT ms.month, ms.total_sales
FROM monthly_sales ms, max_sales ms_max
WHERE ms.total_sales = ms_max.max_sales;
总结
公共表表达式（CTE）是 PostgreSQL 中一种强大的工具，能够简化复杂查询并提高代码的可读性。通过将查询分解为多个步骤，CTE 使得处理复杂逻辑变得更加容易。无论是简单的数据过滤还是递归查询，CTE 都能提供清晰且高效的解决方案。
提示：在使用 CTE 时，尽量保持每个 CTE 的逻辑简单明了，这样可以更容易地调试和维护查询。
附加资源
练习
- 使用 CTE 编写一个查询，找出
products表中价格高于平均价格的产品。 - 使用递归 CTE 查询
categories表中的所有子类别及其层级关系。
通过练习这些示例，你将更好地掌握 PostgreSQL 中的公共表表达式。