oracle的一条查询语句

Question

环境：在scott用户下
表名：EMP表查询各部门中平均工资最高的那个部门的所有人员信息。

Answer 1

 部门平均工资：
 select deptno,avg(sal) from emp group by deptno 
 最高的平均工资：
 select deptno,max(平均工资) from emp;接下来该怎么写？

Answer 2

select *
from emp
where depno in (select depno
from (select a.*,row_number() over(order by avg_sal desc) rn
from (select deptno,avg(sal) avg_sal from emp group by deptno) a
) b
where rn<=1
)

Answer 3

select * from emp where deptno in(
select deptno from emp,
(select max(sal) s from emp) p
where emp.sal = p.s
)

Answer 4

select * from emp where deptno in(
select deptno from 
(select deptno,avg(sal) av_sal from emp group by deptno ) t2
where t2.av_sal in (
select max(av_sal) from
(select deptno,avg(sal) av_sal from emp group by deptno ) t1 )
)

Answer 5

楼主试一试下面的SQL语句是不是你想要的SQL语句：SELECT * FROM EMP oe WHERE EXISTS(
SELECT 1 FROM(
       SELECT t.deptno,avg(t.sal) avg_sal,rank() over(ORDER BY avg(t.sal) DESC) INDEX_ORDER FROM emp t
       GROUP BY t.deptno) a
       WHERE INDEX_ORDER = 1
       AND oe.deptno = a.deptno)这里是通过RANK()进行统计后的数据反向排序，并获取排在第一位的数据（这里不使用ROW_NUMER()的目的是因为可能平均工资存在相同的情况，将这个平均工资相同的不同部门的人都显示出来，所以采用RANK()），然后使用EXISTS而没有直接使用“=”的原因也是如此，因为可能存在两个或两个以上的部门平均工资是一样的。付：这里没有对精度进行控制，如向上取整、向下取整、四舍五入等要求，若有要求，请楼主稍微处理下应该就OK了。

Answer 6

部分数据测试如下：SQL> SELECT * FROM EMP oe WHERE EXISTS(
  2  SELECT 1 FROM(
  3         SELECT t.deptno,avg(t.sal) avg_sal,rank() over(ORDER BY avg(t.sal) DESC) INDEX_ORDER FROM emp t
  4         GROUP BY t.deptno) a
  5         WHERE INDEX_ORDER = 1
  6         AND oe.deptno = a.deptno) ;EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7934 MILLER     CLERK      7782 1982-01-23    1300.00               10
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7782 CLARK      MANAGER    7839 1981-06-09    2450.00               10SQL> SELECT t.deptno,avg(t.sal) avg_sal,rank() over(ORDER BY avg(t.sal) DESC) INDEX_ORDER FROM emp t
  2         GROUP BY t.deptno;DEPTNO    AVG_SAL INDEX_ORDER
------ ---------- -----------
    10 2916.66666           1
    20       2175           2
    30 1566.66666           3

Answer 7

部门平均工资：
select a.*
from emp a,
(select deptno from (select deptno,avg(sal) v_sal from emp group by deptno 
order by avg(sal) desc ) where rownum<=1) t
where a.deptno=t.deptno