如何在后续查询中访问FROM子查询定义的关联名作为表名？

基于university_book数据库的instructor表（结构：instructor (id, name, dept_name, salary)），需求是找出所有总薪资高于所有部门总薪资平均值的部门。

你最初尝试的查询语句：

select dept_name
from
(
  select dept_name, sum(salary) as sum_salary
  from instructor
  group by dept_name
) T
where T.sum_salary > (select avg(sum_salary) from T);

执行后报错：Error Code: 1146. Table 'university_book.t' doesn't exist。

原因分析 #

在MySQL中，FROM子句内定义的子查询别名（如这里的T），无法在同一层级的WHERE子查询中直接引用。子查询是独立执行的上下文，外层的别名对它不可见，数据库会将T当作物理表查找，因此出现表不存在的错误。

可行解决方案 #

1. 使用公共表表达式（CTE，MySQL 8.0及以上版本支持）

CTE允许你定义一次子查询结果，在整个查询中复用，代码更简洁：

WITH dept_salaries AS (
    SELECT dept_name, SUM(salary) AS sum_salary
    FROM instructor
    GROUP BY dept_name
)
SELECT dept_name
FROM dept_salaries
WHERE sum_salary > (SELECT AVG(sum_salary) FROM dept_salaries);

这里dept_salaries作为CTE名称，定义后可在主查询和子查询中直接调用，避免重复编写相同的子查询逻辑。

2. 使用窗口函数（MySQL 8.0及以上版本支持）

通过窗口函数可以一步计算出所有部门总薪资的平均值，无需额外子查询：

SELECT dept_name
FROM (
    SELECT 
        dept_name,
        SUM(salary) AS sum_salary,
        AVG(SUM(salary)) OVER () AS avg_total_salary
    FROM instructor
    GROUP BY dept_name
) T
WHERE sum_salary > avg_total_salary;

AVG(SUM(salary)) OVER ()会基于所有分组的总薪资计算平均值，直接作为列返回，后续只需做条件判断即可。

3. 重复子查询（兼容所有MySQL版本）

就是你当前使用的方案，将相同的子查询逻辑重复编写并赋予新别名（如S），虽然代码冗余，但能兼容低版本MySQL：

select dept_name
from
(
  select dept_name, sum(salary) as sum_salary
  from instructor
  group by dept_name
) T
where T.sum_salary > (select avg(sum_salary) from 
(
  select dept_name, sum(salary) as sum_salary
  from instructor
  group by dept_name
) S
);

内容的提问来源于stack exchange，提问作者VIKKAS GUPTA