查询Oracle SCOTT模式–最接近平均值的薪资是多少

Question

使用SCOTT模式。我想找一个薪水最接近平均水平的人。

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

上述解决方案是否可以改进？

Answer 1

您使用的是非标准的Oracle功能。子查询中的数据最初没有顺序，但在Oracle中，您可以对其进行排序，以便以后能够应用ROWNUM标准。ROWNUM也是Oracle特有的。

此外，可能有几个员工的工资相同，你只是随意挑选其中一个，而不是全部展示。

以下是如何选择工资最接近标准SQL:中可用工资的员工

select *
from emp
order by abs(sal - avg(sal) over())
fetch first row with ties;

最后一行仅从Oracle 12c起可用。

在旧版本(即Oracle 9i、10i或11g(中，您会对行进行排名：

select empno, ename, sal
from
(
  select empno, ename, sal, rank() over (order by diff) as rnk
  from
  (
    select emp.*, abs(sal - avg(sal) over()) as diff
    from emp
  ) evaluated
) ranked
where rnk = 1;

Answer 2

使用子查询提取员工数据：

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;

Answer 3

with av as (select avg(sal) avgsal from scott.emp)
select emp.*, abs(emp.sal-av.avgsal) dist 
  from scott.emp, av 
  order by dist;

上面看起来对我来说更好，但也许你可以用解析函数得到最好的结果。类似于：

select * from (
  select emp.*, abs(avg(emp.sal) over () - sal) diff 
    from scott.emp order by diff) 
where rownum = 1;