oracle 分组查询

使用SCOTT用户登录

分组查询

求员工工资的平均值和工资总额

SELECT AVG(SAL),SUM(SAL) FROM EMP;

求员工工资的最大值和最小值

 SELECT MAX(SAL),MIN(SAL) FROM EMP;

求员工总数人数

 SELECT COUNT(EMPNO) FROM EMP;  SELECT COUNT(*) FROM EMP;

求出部门数(使用DISTINCT 关键字去掉重复的部门号）

SELECT COUNT(DISTINCT DEPTNO) FROM EMP;

行转列 WM_CONCAT

SET LINESIZE 200
COL 部门中员工的姓名 FOR A60
SELECT DEPTNO 部门号,WM_CONCAT(ENAME) 部门中员工的姓名 FROM EMP GROUP BY DEPTNO;

    部门号 部门中员工的姓名
---------- ------------------------------------------------------------
        10 CLARK,MILLER,KING
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD

分组函数与空值

分组函数会自带忽略空值，可以使用NVL函数使分组函数无法忽略空值。

SELECT COUNT(*),COUNT(COMM) FROM EMP;--COMM字段有空值，所以结果不一样。

  COUNT(*) COUNT(COMM)
---------- -----------
        14           4
SELECT COUNT(*),COUNT(NVL(COMM,0)) FROM EMP;--NVL第一个参数为空返回第二个参数的值，否则返回第一个

  COUNT(*) COUNT(NVL(COMM,0))
---------- ------------------
        14                 14

分组数据 GROUP BY 子句

求每个部门的平均工资，要求结果显示部门号，部门的平均工资

SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO;--在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY 子句中，包含在组函数中的列不必包含在SELECT列表中。

    DEPTNO   AVG(SAL)
---------- ----------
        30 1566.66667
        20       2175
        10 2916.66667

求每个部门的平均工资，要求结果显示部门的平均工资

 SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO;

  AVG(SAL)
----------
1566.66667
      2175
2916.66667

使用多个列分组，按部门，不同的职位，统计员工的工资总额

SELECT DEPTNO,JOB,SUM(SAL) FROM EMP GROUP BY DEPTNO,JOB ORDER BY DEPTNO;

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        20 ANALYST         6000
        20 CLERK           1900
        20 MANAGER         2975
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        5600

过滤分组 HAVING子句 （不能在WHERE子句中使用组函数，在HAVING子句中可以）

求平均工资大于2000的部门

SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) > 2000;

    DEPTNO   AVG(SAL)
---------- ----------
        20       2175
        10 2916.66667

查询10号部门的平均工资 

SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING DEPTNO=10;

    DEPTNO   AVG(SAL)
---------- ----------
        10 2916.66667
SELECT DEPTNO,AVG(SAL) FROM EMP WHERE DEPTNO =10 GROUP BY DEPTNO;

    DEPTNO   AVG(SAL)
---------- ----------
        10 2916.66667
--从SQL优化的角度，尽量使用WHERE；HAVING先分组再过滤，WHERE先过滤，再分组。WHERE使得分组记录数大大降低，从而提高效率。

在分组查询中使用ORDER BY子句

求每个部门的平均工资，要求显示部门号，部门的平均工资，并且按照工资升序排列

SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO ORDER BY AVG(SAL);

    DEPTNO   AVG(SAL)
---------- ----------
        30 1566.66667
        20       2175
        10 2916.66667

分组函数嵌套

求部门平均工资的最大值

SELECT MAX(AVG(SAL)) FROM EMP GROUP BY DEPTNO;

MAX(AVG(SAL))
-------------
   2916.66667

GROUP BY 增强(适合报表）

SELECT DEPTNO,JOB,SUM(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO,JOB);
BREAK ON DEPTNO SKIP 2
SET PAGESIZE 30
  DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 CLERK           1300
           MANAGER         2450
           PRESIDENT       5000
                           8750


        20 CLERK           1900
           ANALYST         6000
           MANAGER         2975
                          10875


        30 CLERK            950
           MANAGER         2850
           SALESMAN        5600
                           9400


                          29025
--GROUP BY ROLLUP(DEPTNO,JOB)=GROUP BY DEPTNO,JOB+GROUP BY DEPTNO+GROUP BY NULL(不按照任何条件分组）