子查询与 CTE

CTE（Common Table Expression）

CTE 用 WITH 定义临时结果集，让复杂查询的逻辑分层更清晰。你可以把它理解为"给子查询起个名字，放在前面"。

WITH first_order AS (
    SELECT user_id, MIN(order_time) AS first_time
    FROM orders
    GROUP BY user_id
)
SELECT f.user_id, f.first_time, o.amount
FROM first_order f
JOIN orders o
    ON f.user_id = o.user_id
    AND f.first_time = o.order_time;

CTE vs 嵌套子查询

同样的逻辑用嵌套子查询写：

SELECT f.user_id, f.first_time, o.amount
FROM (
    SELECT user_id, MIN(order_time) AS first_time
    FROM orders
    GROUP BY user_id
) f
JOIN orders o
    ON f.user_id = o.user_id
    AND f.first_time = o.order_time;

两种写法语义完全等价，但 CTE 的优势在于：

• 可读性 — 逻辑从上往下读，不用从最内层子查询往外剥
• 可复用 — 同一个 CTE 在后面的查询里可以引用多次
• 链式定义 — 多个 CTE 用逗号隔开，前面的 CTE 可以被后面的引用

链式 CTE

WITH
step1 AS (
    SELECT user_id, MIN(order_time) AS first_time
    FROM orders GROUP BY user_id
),
step2 AS (
    SELECT s.user_id, s.first_time, o.amount AS first_amount
    FROM step1 s
    JOIN orders o ON s.user_id = o.user_id AND s.first_time = o.order_time
),
step3 AS (
    SELECT user_id, AVG(amount) AS avg_amount
    FROM orders GROUP BY user_id
)
SELECT s2.user_id, s2.first_amount, s3.avg_amount
FROM step2 s2
JOIN step3 s3 ON s2.user_id = s3.user_id
WHERE s2.first_amount > s3.avg_amount;

每一步都有明确的语义命名，比嵌套三四层的子查询清晰得多。面试中写 CTE 也更容易让面试官看懂你的思路。

练习

WITH first_ord AS (
  SELECT user_id, MIN(order_time) AS first_time
  FROM orders GROUP BY user_id
),
first_amount AS (
  SELECT f.user_id, o.amount AS first_amt
  FROM first_ord f
  JOIN orders o ON f.user_id = o.user_id AND f.first_time = o.order_time
),
avg_amount AS (
  SELECT user_id, ROUND(AVG(amount), 1) AS avg_amt
  FROM orders GROUP BY user_id
)
SELECT fa.user_id, fa.first_amt, aa.avg_amt
FROM first_amount fa
JOIN avg_amount aa ON fa.user_id = aa.user_id
WHERE fa.first_amt > aa.avg_amt;