orcale查询平均工资最高的的部门平均工资及部门名称

发布网友 发布时间：2022-04-10 17:54

我来回答

共3个回答

懂视网 时间：2022-04-10 22:15

e.departmentid,d.name,AVG(e.salary) avgsal FROM employee e INNER JOIN department d ON e.departmentid =d.id GROUP BY e.departmentid,d.`name` HAVING avgsal = ( SELECT MAX(t.avgsal) maxsal FROM ( SELECT departmentid,AVG(salary) avgsal FROM employee GROUP BY departmentid) t )

结果图：

 

Mysql案例5：取得平均薪资最高的部门的部门名称

标签：员工   mys   9.png   sel   语句   nbsp   mic   XSA   style   

热心网友 时间：2022-04-10 19:23

1 找出平均工资最高的部门号deptno和对应的平均工资avg(sal)
select * from (select avg(sal) s,deptno from emp group by deptno order by avg(sal) desc) where rownum=1;
2 将第一不得到的结果集作为表b,并且和dept表中的deptno进行关联，取出dept表中的dname
select a.dname, b.s
from dept a,
(select *
from (select avg(sal) s, deptno
from emp
group by deptno
order by avg(sal) desc)
where rownum = 1) b
where a.deptno = b.deptno;来自：求助得到的回答

热心网友 时间：2022-04-10 20:41

select d.dname from dept d where d.deptno=(select deptno from (select avg(sal) a, deptno from emp where sal>0 group by deptno order by a desc )where rownum=1 )