Coding Interview Prep · 课时

FROM 子句中的子查询（派生表）

Q: 「FROM 子句中的子查询（派生表）」这节课中我会学到什么？

将查询包装为虚拟表，并了解为什么别名是必需的 你通过在浏览器中直接运行的动手代码来练习 Coding Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

将查询包装为虚拟表，并了解为什么别名是必需的

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

什么是派生表

FROM 子句中的子查询称为派生表（或内联视图）。它不会返回单独的值，而是返回完整的结果集，外层查询会将其当作真实表来处理。

它可以包含多行和多列。
您可以像查询、连接和筛选任何表一样操作它。

面试官会使用派生表来测试您是否能够将问题拆分成多个阶段。

别名是强制的

最常见的陷阱是：派生表必须有别名。没有别名时，大多数数据库引擎都会拒绝该查询。

MySQL：每个派生表都必须拥有自己的别名。
Postgres：FROM 中的子查询必须有别名。

请为它指定一个名称（这里是 dept_avg），这样您就可以通过该名称引用其中的列。

SELECT dept_avg.dept_id, dept_avg.avg_salary
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS dept_avg;

为什么要在派生表中预聚合

面试中经常出现的一道题是：显示每位员工及其所在部门的平均工资。如果不进行分组，就不能直接将明细行与聚合结果混合使用。

清晰的做法是在派生表中计算每个部门的平均值，然后将它连接回明细行。派生表会先将数据汇总为每个部门一行。

SELECT e.name, e.salary, d.avg_salary
FROM employees e
JOIN (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS d ON e.dept_id = d.dept_id;

筛选聚合结果

派生表可以让您筛选计算得到的聚合结果，而不必在外层查询中使用 HAVING 的繁琐写法。假设我们只想要平均工资超过 60000 的部门。

我们先在内层进行聚合，然后在外层对派生列使用普通的 WHERE。对外层查询来说，avg_salary 就是一个普通列。

SELECT dept_id, avg_salary
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS d
WHERE avg_salary > 60000;

两级聚合

当您需要计算聚合结果的聚合时，派生表尤其有用——面试中经典的问题是：每个部门的平均工资的平均值是多少？

您不能直接嵌套 AVG(AVG(...))。内层查询为每个部门生成一个平均值，外层查询再对这些平均值求平均。

SELECT AVG(avg_salary) AS avg_of_dept_avgs
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS d;

为计算列命名

派生表中的任何表达式，如果希望在外部引用，都需要一个别名。否则，内层的 salary * 12 会使用数据库分配的名称，而这个名称并不可靠。

请始终为计算列设置别名——当面试官发现您引用了一个没有别名的表达式，并假定了一个可能不存在的列名时，通常会对此留下印象。

SELECT name, annual_salary
FROM (
  SELECT name, salary * 12 AS annual_salary
  FROM employees
) AS yearly
WHERE annual_salary > 100000;

连接两个派生表

您可以将多个派生表连接在一起。这里通过连接两个预先聚合的子查询，比较每个部门的员工人数和工资总额。

每个派生表负责回答一个子问题；连接操作将它们拼接成最终报表。这种分阶段思考的方式正是中级岗位面试所看重的。

SELECT c.dept_id, c.headcount, p.payroll
FROM (
  SELECT dept_id, COUNT(*) AS headcount
  FROM employees GROUP BY dept_id
) AS c
JOIN (
  SELECT dept_id, SUM(salary) AS payroll
  FROM employees GROUP BY dept_id
) AS p ON c.dept_id = p.dept_id;

作用域：外层查询无法看到内部内容

一条重要规则是：外层查询只能引用派生表在其 SELECT 列表中公开的列。只在子查询内部使用的列，在外部是不可见的。

如果内层查询选择了 dept_id 和 avg_salary，那么 salary 或 name 在外部就不可用——它们已经被聚合所使用。面试官会通过提问来考察您对这个作用域边界的理解。

SELECT dept_id, avg_salary
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees GROUP BY dept_id
) AS d;

派生表与 CTE

派生表和公用表表达式（CTE）通常会生成相同的执行计划。面试官可能会问您为什么选择其中一种：

派生表：内联使用，适合一次性场景。
CTE（WITH）：在顶部命名，可读性好，并且在多次引用时可以复用。

对于深度嵌套的逻辑，CTE 流程可以从上到下阅读；派生表则需要从内向外阅读。

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees GROUP BY dept_id
)
SELECT * FROM dept_avg WHERE avg_salary > 60000;

LATERAL / 相关 FROM 子查询

通常，FROM 子查询无法引用外层查询的行。LATERAL（Postgres）或 CROSS APPLY（SQL Server）会解除这一限制，使派生表能够针对每个外层行运行。

这为按行查找前 N 项结果提供了支持。即使是在中级岗位面试中，了解这个关键字的存在，也能体现您具备更高级的知识。

SELECT d.dept_name, top_emp.name, top_emp.salary
FROM departments d
CROSS JOIN LATERAL (
  SELECT name, salary FROM employees e
  WHERE e.dept_id = d.id
  ORDER BY salary DESC LIMIT 1
) AS top_emp;

面试金句

如果被问到 FROM 子句中的子查询，可以这样回答：“派生表是 FROM 中的一个子查询，它返回一个结果集，供外层查询像使用表一样使用。它必须有别名，外层查询只能看到它所选择的列；它非常适合在连接前进行预聚合，或对聚合结果再次聚合。”

再补充说明，LATERAL 允许它引用外层行，这样就面面俱到了。

快速检查

选择 FROM 子句中的子查询始终必须具备的条件。

回顾

派生表要点：

FROM 子查询返回一个虚拟表 — 包含多行、多列。
它必须有别名；外层查询只能看到它所选择的列。
可以使用它在连接前进行预聚合、根据聚合结果进行筛选，或对聚合结果再次聚合。
CTE 是更易读的命名替代方案；LATERAL/CROSS APPLY 允许它引用外层行。

下一节：使用 IN、ANY 和 ALL 的集合成员关系子查询。

常见问题解答

「FROM 子句中的子查询（派生表）」课时是免费的吗？

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

「FROM 子句中的子查询（派生表）」这节课中我会学到什么？

将查询包装为虚拟表，并了解为什么别名是必需的你通过在浏览器中直接运行的动手代码来练习 Coding Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

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

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

「FROM 子句中的子查询（派生表）」课时需要多长时间？

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

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

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

此课程中的所有课时

← 返回 Coding Interview Prep