【Oracle数据库】分组函数

• 分组函数概述

  • 分组函数是对数据行的集合进行操作并按组给出一个结果，这个结果可直接输出，或者用来做判断条件
  • 分组函数的使用

    SELECT [column,] group_function(column)FROM table[WHERE condition][GROUP BY column][HAVING group_function(column)expression[ORDER BY column|group_function(column)expression]

• 分组函数

  • MIN函数和MAX函数

    • MIN([DISTINCT|ALL] column|expression)：返回每组中的最小值
    • MAX([DISTINCT|ALL] column|expression)：返回每组中的最大值
    • MIN和MAX函数可以用于任何数据类型

      -- 查询入职日期最早和最晚的日期SELECT MIN(hiredate),MAX(hiredate)FROM emp

  • SUM函数和AVG函数

    • SUM([DISTINCT|ALL] column|expression)：返回每组的总和
    • AVG([DISTINCT|ALL] column|expression)：返回每组的平均值
    • SUM和AVG函数只能够对数值类型的列或表达式操作

      -- 查询职位以SALES开头的所有员工的平均工资、工资和SELECT AVG(sal),SUM(sal)FROM empWHERE job LIKE 'SALES%'

  • COUNT函数

    • COUNT(*)：返回表中满足条件的行记录数

      -- 查询部门30有多少个员工SELECT COUNT(*)FROM empWHERE deptno=30

    • COUNT([DISTINCT|ALL] column|expression)：返回表中满足条件的非空行记录数

      -- 查询部门30有多少个员工领取奖金SELECT COUNT(comm)FROM empWHERE deptno=30

  • 分组函数中的DISTINCT

    DISTINCT会消除重复记录后再使用组函数

    -- 查询有员工的部门数量SELECT COUNT(DISTINCT deptno)FROM emp

  • 分组函数中的空值处理

    • 除了COUNT(*)之外，其他所有分组函数都会忽略列表中的空值，然后再进行计算

      -- 查询奖金的平均值SELECT AVG(comm)FROM emp

    • 在分组函数中使用NVL函数可以使分组函数强制包含含有空值的记录

      -- 查询所有员工领取到的奖金的平均值SELECT AVG(NVL(comm,0))FROM emp

• GROUP BY子句

  • 创建数据组

    • 使用GROUP BY子句可将表中满足WHERE条件的记录按照指定的列划分成若干个小组，其中GROUP BY子句指定要分组的列

      -- 用GROUP BY子句创建数据组SELECT column,group_function(column)FROM table[WHERE condition][GROUP BY group_by_expression][ORDER BY column]

  • 使用GROUP BY子句

    • 在SELECT列表中除了分组函数那些项，所有列都必须包含在GROUP BY子句中

      SELECT deptno,AVG(sal)FROM empGROUP BY deptno

    • GROUP BY所指定的列并不是必须出现在SELECT列表中

      SELECT AVG(sal)FROM empGROUP BY deptno

    • 按多列分组的GROUP BY子句

      SELECT deptno,job,SUM(sal)FROM empGROUP BY deptno,job

• HAVING子句

  • 不能在WHERE子句中限制组，要在HAVING子句中对数据组进行限制

    -- 用HAVNG子句限制数据组SELECT column,group_function(column)FROM table[WHERE condition][GROUP BY group_by_expression][HAVING group_condition][ORDER BY column]

  • 使用HAVING子句限制数据组

    SELECT deptno,MAX(sal)FROM empGROUP BY deptnoHAVING MAX(sal)>2900

• SELECT语句执行过程

  1. 通过FROM子句中找到需要查询的表
  2. 通过WHERE子句进行非分组函数筛选判断
  3. 通过GROUP BY子句完成分组操作
  4. 通过HAVING子句完成组函数筛选操作
  5. 通过SELECT子句选择显示的列或表达式及组函数
  6. 通过ORDER BY子句进行排序操作

• 组函数的嵌套

  与单行函数不同，组函数只能嵌套两层

  SELECT MAX(AVG(sal))FROM empGROUP BY deptno