sql - 查询 Oracle SCOTT 模式 - 最接近平均水平的薪水是多少？

Question

使用 SCOTT Schema .我试图找到薪水最接近平均水平的人。

SELECT sal
      FROM (  SELECT sal
                FROM emp
            ORDER BY ABS ( (SELECT AVG (SAL) FROM EMP) - sal))
     WHERE ROWNUM = 1;

上面的解决方案可以改进吗？

Answer 1

使用子查询拉回员工数据:

select empno, sal, SalDif
from 
  (
    select empno, sal, abs(sal - (select avg(sal) from emp)) as SalDif
    from emp
    order by SalDif
  )
where rownum = 1;

或作为 CTE:

with CTE as
  (
    select empno, sal, abs(sal - (select avg(sal) from emp)) as SalDif
    from emp
    order by SalDif
  )
select empno, sal, SalDif
from CTE
where rownum = 1;