在PostgreSQL数据库中，公用表表达式（Common Table Expressions，简称CTE）是一种强大的查询工具，它允许我们定义临时的结果集，这些结果集可以在后续的查询中被引用。CTE提供了一种更加清晰和结构化的方式来编写复杂查询，特别是当查询涉及递归操作或多层嵌套时。
什么是CTE
公用表表达式是一种临时的结果集，它在查询执行期间存在，并且只对当前查询可见。CTE使用WITH关键字定义，可以看作是"内联视图"或"临时表"，但与传统子查询相比，它具有更好的可读性和维护性。
CTE的基本语法
CTE的基本语法结构如下：
WITH cte_name AS (
-- CTE查询定义
SELECT column1, column2, ...
FROM table_name
WHERE conditions
)
-- 主查询
SELECT * FROM cte_name
WHERE conditions;
CTE的优势
1. 提高查询可读性
相比嵌套的子查询，CTE让查询逻辑更加清晰。每个CTE可以被命名，使得复杂查询的各个部分更容易理解。
2. 避免重复计算
当同一个子查询在主查询中被多次引用时，使用CTE可以避免重复计算，提高查询效率。
3. 支持递归查询
CTE最强大的特性之一是支持递归查询，这对于处理层次结构数据（如组织架构、分类树等）非常有用。
实际应用案例
让我们通过几个实际案例来深入了解CTE的使用方法：
案例1：基本CTE使用
假设我们有一个员工表，需要查询薪资高于平均薪资的员工信息：
WITH avg_salary AS (
SELECT AVG(salary) AS avg_sal
FROM employees
)
SELECT e.name, e.salary, e.department
FROM employees e
CROSS JOIN avg_salary a
WHERE e.salary > a.avg_sal;
在这个例子中，我们首先计算出所有员工的平均薪资，然后在主查询中使用这个结果来筛选出薪资高于平均值的员工。
案例2：多CTE使用
当查询逻辑较为复杂时，我们可以定义多个CTE：
WITH high_earners AS (
SELECT employee_id, name, salary
FROM employees
WHERE salary > 80000
),
department_stats AS (
SELECT department_id,
COUNT(*) as emp_count,
AVG(salary) as avg_dept_salary
FROM employees
GROUP BY department_id
)
SELECT h.name, h.salary, d.emp_count, d.avg_dept_salary
FROM high_earners h
JOIN employees e ON h.employee_id = e.employee_id
JOIN department_stats d ON e.department_id = d.department_id;
这个查询首先定义了两个CTE：一个是高收入员工列表，另一个是各部门的统计信息，然后在主查询中将它们结合起来。
案例3：递归CTE
递归CTE是处理层次结构数据的强大工具。例如，查询组织架构中的所有下属员工：
WITH RECURSIVE subordinates AS (
-- 锚点查询：查找特定经理的直接下属
SELECT employee_id, name, manager_id, 1 as level
FROM employees
WHERE manager_id = 100
UNION ALL
-- 递归部分：查找下属的下属
SELECT e.employee_id, e.name, e.manager_id, s.level + 1
FROM employees e
INNER JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates
ORDER BY level, name;
在这个递归CTE中，锚点查询首先找到经理ID为100的直接下属，然后递归部分不断查找这些下属的下属，直到没有更多的记录为止。
CTE与子查询的对比
虽然CTE在很多场景下可以替代子查询，但两者各有优势：
- 可读性：CTE通常比嵌套子查询更容易阅读和理解
- 重用性：CTE可以在查询中被多次引用，而子查询每次都需要重新计算
- 调试性：CTE可以单独测试，便于调试复杂查询
- 性能：在某些情况下，优化器可能对子查询有更好的优化策略
注意事项和最佳实践
1. 性能考虑
虽然CTE提高了可读性，但在某些情况下可能会影响性能。特别是在递归CTE中，需要合理设置递归深度限制，避免无限递归。
2. 命名规范
给CTE起有意义的名称，使其能够清晰表达其用途，这有助于提高代码的可维护性。
3. 适度使用
不要为了使用CTE而使用它。对于简单的查询，直接使用子查询可能更加合适。
总结
PostgreSQL中的CTE是一个非常有用的特性，它不仅提高了查询的可读性和维护性，还支持强大的递归查询功能。通过合理使用CTE，我们可以编写出更加清晰、高效的SQL查询。在处理复杂的数据分析和层次结构查询时，CTE往往是最佳选择。掌握CTE的使用技巧，对于数据库开发人员来说是一项重要的技能。
