Coding Interview Prep · 课时

编写第一个 CTE

Q: 「编写第一个 CTE」这节课中我会学到什么？

掌握基本 WITH 语法，以及 CTE 何时比子查询更易读 你通过在浏览器中直接运行的动手代码来练习 Coding Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

掌握基本 WITH 语法，以及 CTE 何时比子查询更易读

编写第一个 CTE 是 CoddyKit 上的免费 Coding Interview Prep 课时。这是第 1 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 Coding Interview Prep 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Coding Interview Prep 课程共包含 4 节课。

CTE 的实际含义

通用表表达式（CTE）是使用 WITH 关键字定义的、具有名称的临时结果集，其生命周期仅限于单个查询。面试官喜欢考察 CTE，因为这能体现您是否能够清晰地组织逻辑。

您可以把 CTE 理解为给子查询命名，这样就能在后续的主语句中像引用表一样引用它。它不会创建永久对象，并会在查询完成的瞬间消失。

基本的 WITH 语法

每个 CTE 都以 WITH 开始，后接名称、关键字 AS 和用括号包围的查询。在右括号之后，您要编写一条通过名称使用该 CTE 的普通语句。

WITH cte_name AS ( ... ) 定义这个代码块。
紧跟右括号之后的查询就是主查询。
CTE 的名称表现得像一张表，您可以从中执行 SELECT。

WITH recent_orders AS (
    SELECT *
    FROM orders
    WHERE order_date >= '2024-01-01'
)
SELECT *
FROM recent_orders;

为什么不直接使用子查询

同样的逻辑也可以写成 FROM 子句中的内联子查询。那么，面试官为什么会询问 CTE？

可读性：具有名称的步骤从上到下阅读起来就像一份操作说明。
复用：您可以多次引用同一个 CTE，而不必重复编写子查询。
可调试性：您可以只对 CTE 执行 SELECT 来检查它。

面试中通常应这样回答：当 CTE 能让查询更易读、更易维护时，就使用 CTE。

示例：先筛选再聚合

假设问题是：找出今年下单产生的总收入。 CTE 可以将筛选步骤单独提取出来，然后对这个具有名称的结果进行聚合。

主查询将 recent_orders 当作真实表来处理，从而让聚合逻辑保持简洁明了。

WITH recent_orders AS (
    SELECT amount
    FROM orders
    WHERE order_date >= '2024-01-01'
)
SELECT SUM(amount) AS total_revenue
FROM recent_orders;

命名输出列

您可以在 CTE 名称后立即列出列名，以重命名 CTE 对外提供的列。当内层查询生成表达式，或者您希望后续逻辑使用更清晰的名称时，这会很方便。

如果您提供了列名列表，它必须与内层查询返回的列数一致，否则数据库会引发错误。

WITH revenue (region, total) AS (
    SELECT region, SUM(amount)
    FROM orders
    GROUP BY region
)
SELECT region, total
FROM revenue
ORDER BY total DESC;

CTE 只是一个具有名称的查询

有一种能给面试官留下深刻印象的理解方式：从逻辑上说，CTE 等价于将其定义内联替换。数据库可以选择将其内联或物化，但从语义上看，结果与直接粘贴该子查询完全相同。

这意味着，普通 SELECT 中允许使用的内容，在 CTE 内部也都允许使用：连接、GROUP BY、WHERE、窗口函数等。

深入示例：CTE 与连接

当您需要预先整理连接的一侧时，CTE 尤其有用。这里我们先构建每位客户的订单数量，然后将其连接回客户表，使每个客户行都带有自己的总数。

请注意，主查询读起来几乎就像中文说明：从客户表出发，连接其订单数量。

WITH order_counts AS (
    SELECT customer_id, COUNT(*) AS num_orders
    FROM orders
    GROUP BY customer_id
)
SELECT c.name, oc.num_orders
FROM customers c
JOIN order_counts oc
    ON oc.customer_id = c.id;

CTE 在查询中的作用域

WITH 代码块必须位于使用它的语句之前。CTE 仅对其定义之后紧接着的那一条语句可见。

您不能在之后单独执行的查询中引用 CTE。
为一个 SELECT 定义的 CTE 不能被之后执行的另一个 SELECT 复用。
作用域在终止该语句的分号处结束。

CTE 可与 INSERT、UPDATE、DELETE 一起使用

一个常见的后续问题是：CTE 并不局限于 SELECT。在大多数现代数据库中，您也可以将 WITH 子句附加到数据修改语句上。

这样，您就可以先计算出一组行，然后对它们执行操作；与在 WHERE 子句中嵌套子查询相比，这种写法清晰得多。

WITH stale AS (
    SELECT id
    FROM sessions
    WHERE last_seen < NOW() - INTERVAL '30 days'
)
DELETE FROM sessions
WHERE id IN (SELECT id FROM stale);

初学者常犯的错误

面试官会留意以下错误：

忘记在 CTE 后编写主查询；单独的 WITH 代码块不是完整的语句。
在 CTE 和主查询之间放置分号。
误以为 CTE 会在多条语句之间持续存在。
可选列名列表与内层查询的列不匹配。

面试中如何谈论 CTE

当被要求重构混乱的子查询时，请讲述您的思路：我会将这个筛选子查询提取到名为 recent_orders 的 CTE 中，这样聚合逻辑会更清晰。

说明您选择 CTE 是为了清晰性和复用，而不是盲目使用，这体现了中级水平。请提到 CTE 本身不会让查询更快；它的主要价值在于可读性。

快速检查

请检查您对基本 CTE 语法和作用域的理解。

回顾：您的第一个 CTE

您学会了使用 WITH name AS ( ... ) 为临时结果集命名，然后在后续语句中像引用表一样引用它。

与内联子查询相比，CTE 能提升可读性、复用性和可调试性。
作用域仅限于一条语句；之后便会消失。
它们可与 SELECT 以及 INSERT/UPDATE/DELETE 配合使用。
它们本身不会提升性能。

接下来：将多个 CTE 串联成一条流水线。

常见问题解答

「编写第一个 CTE」课时是免费的吗？

是的 — 「编写第一个 CTE」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Coding Interview Prep 课程的其余内容，请升级到 CoddyKit PRO。 Coding Interview Prep 课程共包含 4 节课。

「编写第一个 CTE」这节课中我会学到什么？

掌握基本 WITH 语法，以及 CTE 何时比子查询更易读你通过在浏览器中直接运行的动手代码来练习 Coding Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 Coding Interview Prep 需要有经验吗？

无需任何先前经验。CoddyKit 上的 Coding Interview Prep 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 1 节课，共 4 节。

「编写第一个 CTE」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 Coding Interview Prep 课中编写并运行代码吗？

能。每节 Coding Interview Prep 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 Coding Interview Prep