12.3 FROM子句中的子查询

      在之前的章节中，我们学习了如何在 WHERE 子句中使用子查询来过滤数据。除了 WHERE，子查询还可以出现在 FROM 子句中。这时，子查询的结果会充当一个临时表（也称为派生表或内联视图），外部查询可以像操作普通表一样对这个临时表进行进一步查询。

一、什么是派生表？

当子查询位于 FROM 子句后时，它扮演了“数据源”的角色。这个子查询必须有一个别名，以便外部查询引用它的列。派生表在逻辑上是一个虚拟的临时表，仅在查询执行期间存在。

基本语法如下：

SELECT 列名
FROM (子查询) AS 别名
[WHERE 条件]
[GROUP BY ...]
[HAVING ...]
[ORDER BY ...];

注意：AS 关键字可以省略，但别名必须有。

二、基础示例

假设我们有一张 employees 表，包含 id, name, department_id, salary。我们想查询每个部门的平均工资，然后找出平均工资大于 5000 的部门。一种方法是先分组计算平均工资，再用 HAVING 过滤。这里我们用派生表来实现同样的效果：

SELECT dept_id, avg_salary
FROM (
    SELECT department_id AS dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_avg
WHERE avg_salary > 5000;

执行过程：

1. 先执行子查询，计算出每个部门的平均工资，生成一个临时结果集（包含 dept_id 和 avg_salary 两列）。

2. 外部查询从这个临时结果集中选择数据，并应用 WHERE 条件。

虽然这个例子也可以用 HAVING 直接完成，但它展示了派生表的基本用法——将分组统计的结果作为一张新表来继续查询。

三、派生表的典型应用场景

1. 对聚合结果进行再筛选

如上面的例子，有时我们需要对聚合后的结果进行更复杂的筛选，而 HAVING 只能使用聚合函数，不能引用别名或进行多层嵌套。派生表提供了更大的灵活性。

2. 使用窗口函数的结果

MySQL 8.0 支持窗口函数。窗口函数的结果只能在派生表中进一步使用。例如，我们想要查询每个部门工资排名前两名的员工：

SELECT department_id, employee_id, salary, rn
FROM (
    SELECT 
        department_id,
        employee_id,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
    FROM employees
) AS ranked
WHERE rn <= 2;

这里，窗口函数 ROW_NUMBER() 必须在派生表中计算，外部才能使用 rn 列进行过滤。

3. 简化复杂查询，提高可读性

当查询涉及多层嵌套时，派生表可以将复杂的逻辑分步拆解，使最终查询更清晰。例如，先计算每个用户的订单总额，再从中找出总额大于 1000 的用户：

SELECT user_id, total
FROM (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
) AS user_total
WHERE total > 1000;

4. 连接派生表与普通表

派生表可以与其他表进行连接操作。例如，我们想获取每个部门工资最高的员工信息，同时显示部门名称：

SELECT d.dept_name, top.employee_id, top.salary
FROM departments d
JOIN (
    SELECT department_id, employee_id, salary
    FROM employees e1
    WHERE salary = (
        SELECT MAX(salary) 
        FROM employees e2 
        WHERE e2.department_id = e1.department_id
    )
) AS top ON d.id = top.department_id;

四、注意事项与性能考量

1. 必须使用别名

MySQL 要求每个派生表必须有一个别名，即使外部查询没有直接使用这个别名，也必须提供。

2. 派生表的物化

MySQL 在执行包含派生表的查询时，通常会物化派生表，即将其结果存储在内存或磁盘的临时表中。如果派生表的数据量很大，可能会消耗大量资源，影响性能。对于复杂的派生表，可以尝试使用 EXPLAIN 查看执行计划，观察是否有优化空间（例如使用索引或改为 JOIN）。

3. 派生表中可以使用 ORDER BY 吗？

在派生表内部可以使用 ORDER BY，但通常会被外部查询的优化器忽略，除非结合 LIMIT 一起使用。例如，以下查询可能不会按预期排序：

SELECT * FROM (SELECT * FROM employees ORDER BY salary DESC) AS e;

由于外部查询没有 ORDER BY，最终结果的顺序是不确定的。如果需要排序，应该在外部查询中添加 ORDER BY。但如果在派生表中使用 LIMIT，则 ORDER BY 有意义：

SELECT * FROM (SELECT * FROM employees ORDER BY salary DESC LIMIT 10) AS e;

这个查询先找出工资最高的前 10 名员工，外部再取出所有列。

4. 与公用表表达式（CTE）的对比

MySQL 8.0 引入了公用表表达式（CTE），使用 WITH 子句，可以更清晰地定义临时结果集，并可能在同一个查询中多次引用。CTE 通常比派生表更具可读性，尤其是在需要多次引用同一临时结果时。例如：

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

虽然 CTE 功能更强大，但派生表仍是标准 SQL 的一部分，适用于所有 MySQL 版本（包括 5.x）。

五、练习

1、使用派生表，查询订单总额最高的前 3 位客户。

• 表：orders(order_id, customer_id, amount)

2、找出每个分类下商品价格高于该分类平均价的商品。

• 表：products(product_id, category_id, price)

• 提示：先在派生表中计算每个分类的平均价格，再与原表连接。

参考答案（练习1）：

SELECT customer_id, total
FROM (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
) AS cust_total
ORDER BY total DESC
LIMIT 3;

六、小结

• FROM 子句中的子查询 又称为派生表，它把子查询的结果当作临时表供外部查询使用。

• 派生表必须指定别名。

• 常用于对聚合结果进一步筛选、使用窗口函数、分解复杂逻辑。

• 注意派生表可能被物化，影响性能，需合理优化。

• MySQL 8.0 还提供了 CTE，可作为派生表的替代，尤其适合复杂或重复使用的场景。

掌握派生表，你将能够编写更灵活、更强大的 SQL 查询。