Oracle分组查询

group by ... having ... order by ...

1、查询每个岗位的总工资并且不包括'SALESMAN'岗位而且工资和大于5000

SELECT JOB, SUM(SAL) SU
  FROM EMP
 WHERE JOB <> 'SALESMAN'
 GROUP BY JOB
HAVING SUM(SAL) > 5000
 ORDER BY SU DESC;

2、要求查询出：部门名称，部门的员工数，部门的平均工资，部门的最低收入员工的姓名和最高收入员工的姓名

SELECT DEPT.DNAME AS 部门名称,
       TEMP.C     AS 人数,
       TEMP.A     AS 平均工资,
       TEMP.M     AS 最低工资,
       TEMP.MS    AS 最高工资,
       E.ENAME    AS 最低工资人,
       DD.ENAME   AS 最高工资人
  FROM (SELECT DEPTNO, COUNT(*) C, AVG(SAL) A, MIN(SAL) M, MAX(SAL) MS
          FROM EMP
         GROUP BY DEPTNO) TEMP,
       DEPT,
       EMP E,
       EMP DD
 WHERE TEMP.DEPTNO = DEPT.DEPTNO
   AND E.SAL = TEMP.M
   AND E.DEPTNO = TEMP.DEPTNO
   AND DD.SAL = TEMP.MS
   AND DD.DEPTNO = TEMP.DEPTNO
 ORDER BY 1;

转载于:https://www.cnblogs.com/nuaa/p/3691635.html