mysql - 如何解决此 SQL 错误 : Every derived table must have its own alias?

Question

这个 SQL 有什么问题:

SELECT dept_name, avg_salary
FROM    (SELECT dept_name, AVG(salary) AS avg_salary                
        FROM instructor GROUP BY dept_name)
WHERE avg_salary > 42000;

我收到这个错误:

ERROR 1248 (42000): Every derived table must have its own alias

有人帮助我，谢谢。

Answer 1

通过添加如下别名

SELECT dept_name, avg_salary
FROM    (SELECT dept_name, AVG(salary) AS avg_salary                
        FROM instructor GROUP BY dept_name) xxx <-- Here
WHERE avg_salary > 42000;

尽管您可以像下面这样修改您的查询

SELECT dept_name, AVG(salary) AS avg_salary 
FROM  instructor 
GROUP BY dept_name
HAVING AVG(salary) > 42000;