sql - 平均 sal 然后按降序排序

Question

我想列出所有收入超过平均工资的员工。我对此有点迷茫。我需要将所有薪水加起来然后取平均，只显示收入高于平均水平的薪水。在这方面我需要很多帮助。

我的查询不起作用

 SQL> select empno, ename, job, hiredate, sal, deptno from emp where sal avg(sal);
 select empno, ename, job, hiredate, sal, deptno from emp where sal avg(sal)
                                                               *
 ERROR at line 1:
 ORA-00920: invalid relational operator
 SQL>

表格

SQL> select empno, ename, job, hiredate, sal, deptno from emp;

 EMPNO ENAME      JOB       HIREDATE         SAL     DEPTNO
 ---------- ---------- --------- --------- ---------- ----------
  7839 KING       PRESIDENT 17-NOV-81       5000         10
  7698 BLAKE      MANAGER   01-MAY-81       2850         30
  7782 CLARK      MANAGER   09-JUN-81       2450         10
  7566 JONES      MANAGER   02-APR-81       2975         20
  7654 MARTIN     SALESMAN  28-SEP-81       1250         30
  7499 ALLEN      SALESMAN  20-FEB-81       1600         30
  7844 TURNER     SALESMAN  08-SEP-81       1500         30
  7900 JAMES      CLERK     03-DEC-81        950         30
  7521 WARD       SALESMAN  22-FEB-81       1250         30
  7902 FORD       ANALYST   03-DEC-81       3000         20
  7369 SMITH      CLERK     17-DEC-80        800         20

 EMPNO ENAME      JOB       HIREDATE         SAL     DEPTNO
 ---------- ---------- --------- --------- ---------- ----------
  7788 SCOTT      ANALYST   09-DEC-82       3000         20
  7876 ADAMS      CLERK     12-JAN-83       1100         20
  7934 MILLER     CLERK     23-JAN-82       1300         10

 14 rows selected.

Answer 1

您收到无效的关系运算符错误，因为您省略了 less than登录您的查询。请学会阅读the documentation.

除此之外，您还需要编写一个子查询来计算平均工资。所以你的查询应该是这样的:

select empno, ename, job, hiredate, sal, deptno 
from emp 
where sal > (select  avg(sal) from emp)
order by sal desc;