我正在使用SQL编写以下查询:
select MAX(AVG(salary) ) from employees group by department_id;
首先我将通过 department_id
获取组,但接下来会发生什么?
select MAX(AVG(salary) ) from employees group by department_id;
首先我将通过 department_id
获取组,但接下来会发生什么?
如果你有类似这样的东西
EmployeeId DepartmentId Salary
1 1 10
2 1 30
3 2 30
4 2 40
5 2 20
6 3 40
7 3 50
分组后
DepartmentId AVG(Salary)
1 (10+30)/2 = 20
2 (30+40+20)/3 = 30
3 (40+50)/2= 45
因此,下面的查询将返回部门编号为3的最大平均工资为45。
SELECT MAX(x.avg)
FROM ( SELECT AVG(salary)as avg FROM employees group by department_id)x;
select max(AveragesByDept.avgSalary)
from (
select avgSalary=avg(salary)
from employees
group by department_id
) AveragesByDept
select AVG(salary)
from employees
group by department_id
order by avg(salary) desc
limit 1
< p > limit 1
可能是 top 1
(SQL Server) 或带有 rownum = 1
的子查询 (Oracle)。
这取决于关系型数据库管理系统的支持情况,但如果支持,您将获得部门平均工资中最高的。
group by 应用于内部聚合,而外部聚合未分组。