表:员工
EmpID: INTEGER NOT NULL
DeptID: INTEGER NOT NULL
Name: Char(40) NOT NULL
Age: INTEGER NOT NULL
Salary: INTEGER NOT NULL
表:部门
DeptID: INTEGER NOT NULL
Name: CHAR(20) NOT NULL
我们需要编写一个SQL查询,该查询输出所有部门的名称,以使该部门的平均工资大于10,000,000。输出应仅包含一列:
DeptName
。试过这个:
SELECT AVG(Salary) AS avg_salary ,DeptID FROM ( SELECT DeptID, Salary FROM employee GROUP BY 1) DeptID GROUP BY DeptID HAVING avg_salary > 10;
结果:无法加入。
最佳答案
这是查询:
SELECT
Department.name AS DeptName
FROM Department
INNER JOIN Employee ON Department.DeptID = Employee.DeptID
GROUP BY Department.DeptID
HAVING AVG(Employee.Salary) > 1000000;
关于mysql - sql查询按部门名称分组,其他表中有详细信息,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/37517540/