CTE vs 子查询：什么时候选哪个？

我们已经知道CTE让代码更好读。但是不是每次都该用它？有时候简单的子查询反而更快更好。来看看每种工具各自适合什么场景，学会怎么做出明智选择。

子查询：快又简单

你应该记得，子查询就是SQL里的SQL。它直接嵌在主查询里，"就地"执行。很适合一次性的简单操作：

-- 找出价格高于平均价的商品
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

这里子查询只算一次平均价，搞定收工。没有多余的结构。

性能：谁更快？

子查询在简单操作时经常更快。PostgreSQL可以"即时"优化，尤其是子查询只执行一次时：

-- 快：子查询只执行一次
SELECT customer_id, order_total
FROM orders
WHERE order_date = (SELECT MAX(order_date) FROM orders);

CTE默认会被物化 —— PostgreSQL会先算出CTE结果，存成临时表，再用它。这可能让简单查询变慢：

-- 慢一点：CTE会被物化成临时表
WITH latest_date AS (
    SELECT MAX(order_date) AS max_date FROM orders
)
SELECT customer_id, order_total
FROM orders, latest_date
WHERE order_date = max_date;

不过！从PostgreSQL 12开始你可以控制物化：

-- 强制不物化
WITH latest_date AS NOT MATERIALIZED (
    SELECT MAX(order_date) AS max_date FROM orders
)
SELECT customer_id, order_total
FROM orders, latest_date
WHERE order_date = max_date;

多次复用：这里CTE无敌

当你需要多次用到同一个中间结果时，CTE就变得不可替代：

-- 用子查询：同一逻辑写两遍
SELECT
    (SELECT COUNT(*) FROM orders WHERE status = 'completed') AS completed_orders,
    (SELECT COUNT(*) FROM orders WHERE status = 'completed') * 100.0 / COUNT(*) AS completion_rate
FROM orders;

-- 用CTE：只算一次，用两次
WITH completed_orders AS (
    SELECT COUNT(*) AS count FROM orders WHERE status = 'completed'
)
SELECT
    co.count AS completed_orders,
    co.count * 100.0 / (SELECT COUNT(*) FROM orders) AS completion_rate
FROM completed_orders co;

复杂分析：CTE完胜

多步骤分析时，CTE能把混乱变成有序。对比下这个销售报表：

用子查询（脑袋一团浆糊）：

SELECT 
    category,
    revenue,
    revenue * 100.0 / (
        SELECT SUM(p.price * oi.quantity)
        FROM order_items oi
        JOIN products p ON oi.product_id = p.product_id
        JOIN orders o ON oi.order_id = o.order_id
        WHERE EXTRACT(year FROM o.order_date) = 2024
    ) AS revenue_share
FROM (
    SELECT 
        p.category,
        SUM(p.price * oi.quantity) AS revenue
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    JOIN orders o ON oi.order_id = o.order_id
    WHERE EXTRACT(year FROM o.order_date) = 2024
    GROUP BY p.category
) category_revenue;

用CTE（条理清晰）：

WITH yearly_sales AS (
    SELECT 
        p.category,
        p.price * oi.quantity AS sale_amount
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    JOIN orders o ON oi.order_id = o.order_id
    WHERE EXTRACT(year FROM o.order_date) = 2024
),
category_revenue AS (
    SELECT 
        category,
        SUM(sale_amount) AS revenue
    FROM yearly_sales
    GROUP BY category
),
total_revenue AS (
    SELECT SUM(sale_amount) AS total FROM yearly_sales
)
SELECT 
    cr.category,
    cr.revenue,
    cr.revenue * 100.0 / tr.total AS revenue_share
FROM category_revenue cr, total_revenue tr;

递归：CTE独占鳌头

对于层级结构，子查询完全搞不定。

只有递归CTE能搞定比如"找出某个经理的所有下属"这种需求：

WITH RECURSIVE employee_hierarchy AS (
    -- 从CEO开始
    SELECT employee_id, manager_id, name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 加入每一级的下属
    SELECT e.employee_id, e.manager_id, e.name, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy ORDER BY level, name;

调试和维护代码

CTE可以分阶段轻松调试：

-- 检查第一步
WITH active_customers AS (
    SELECT customer_id FROM customers WHERE status = 'active'
)
SELECT COUNT(*) FROM active_customers; -- 确认逻辑没问题

-- 加入第二步
WITH active_customers AS (...),
recent_orders AS (
    SELECT customer_id, COUNT(*) as order_count
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY customer_id
)
SELECT COUNT(*) FROM recent_orders; -- 也检查下这步

子查询调试起来更麻烦——得把它们单独拎出来。

实用建议

用子查询的场景：

• 逻辑简单，一行能写完
• 对简单操作要极致性能
• 中间结果只用一次
• 数据量不大

用CTE的场景：

• 查询很复杂，可以分成逻辑阶段
• 需要多次用到中间结果
• 代码可读性和可维护性很重要
• 处理层级结构（递归CTE）
• 分阶段调试复杂逻辑

黄金法则

先用子查询。如果发现难读或者逻辑重复——就切换到CTE。你未来的同事（或者半年后的你自己）会感谢你的！