Question

oracle中 emp表怎显示每部门每岗位的平均工资,每部门的平均工资,每岗位的平均工资.

诉求！

Answer 1

部门+岗位:select avg(salary),dept_id 部门,gangwei 岗位 from emp group by dept_id,gangwei
部门:select avg(salary),dept_id 部门 from emp group by dept_id
岗位:select avg(salary),gangwei 岗位 from emp group by gangwei

追问

哥们就一条语句哦

Answer 2

部门平均工资
select deptno,avg(sal) as avgsal_dept from emp group by deptno;
岗位平均工资
select job,avg(sal) as avgsal_job from emp group by job;

追问

哥们就一条语句哦

追答

呵呵,没看明白
那就这样：
select deptno,job,avg(sal) from emp group by cube(deptno,job);
你可以多看看rollup和cube的用法。

Answer 3

0)),deptno
from emp
where deptno in(select deptno from emp group by deptno having count(*)>10)
group by deptno;

或是直接
select max(sal+nvl(comm,0)),deptno
from emp
group by deptno
having count(deptno)>10;

（ps:emp表中应该没有哪个部门人数大于10吧！）