Oracle PL/SQL 基础教程（4）复杂查询（一）

SQL干货分享 2021-08-13

875

（CSDN博主：写代码也要符合基本法）

各位看官老爷大家好，有到了周三保留节目 PL/SQL 基础小课堂时间

前面两节课程已经把最基本的单表查询语句做了透彻的讲解，从今天开始的几节课程，我们将探讨如何写出更为复杂的查询语句

接下来开启 PL/SQL 基础课程第四课：数据分组！

概述

顾名思义，“数据分组”就是将结果集中的数据按照某些条件进行划分，在这些条件上具有共同特征的行就划分为同一个组

在 SQL 语句中实现数据分组，所谓的分组条件就对应为若干个“列”，结果集当中这些列的值全部对应相同的行则划分为同一个组，只要有一个指定列的值不同的两行则不能划分到同一个组

注意，这里描述的是列值对应相同，这意味着非空值要彼此相等，而空值和另一个空值亦可视为相同

SELECT 语句中数据分组使用语法关键字 GROUP BY 实现，数据分组最常见的应用场景，也是最主要的设计目的为搭配聚合函数，在各数据组内进行相应的数据计算

GROUP BY 指明根据哪些列进行分组，这些列使用逗号分隔，没有先后顺序

注意这里不得使用 SELECT 中的列的别名，也不能在此声明别名，但是可以引用标量函数或者列的表达式等形式

使用聚合函数

所谓聚合函数是可以一次性作用于数据组中的所有行，并计算返回一个（行）结果的函数

聚合函数与 GROUP BY 搭配使用时，可以出现在 SELECT 列表中，但是不能在 WHERE 条件和 GROUP BY 列表中使用，如果需要根据分组内的聚合结果再进行筛选操作的话，则须使用 HAVING 语法来声明这些筛选条件

SUM、MAX、MIN、AVG 是极为常用的数值聚合函数，分别用于计算组内指定列的总和、最大值、最小值和平均值

SQL> --计算各个部门的最高工资、最低工资和总工资
SQL> SELECT e.deptno, MAX(e.sal) max_sal, MIN(e.sal) min_sal, SUM(e.sal) sum_sal
  2    FROM emp e
  3   GROUP BY e.deptno;


DEPTNO    MAX_SAL    MIN_SAL    SUM_SAL
------ ---------- ---------- ----------
    30       2850        950       9400
    20       3000        800      10875
    10       5000       1300       8750


SQL> --计算各个部门各个岗位的平均工资，注意ORDER BY排序要放在GROUP BY分组之后
SQL> SELECT e.deptno, e.job, AVG(e.sal) avg_sal
  2    FROM emp e
  3   GROUP BY e.deptno, e.job
  4   ORDER BY e.job, e.deptno;


DEPTNO JOB          AVG_SAL
------ --------- ----------
    20 ANALYST         3000
    10 CLERK           1300
    20 CLERK            950
    30 CLERK            950
    10 MANAGER         2450
    20 MANAGER         2975
    30 MANAGER         2850
    10 PRESIDENT       5000
    30 SALESMAN        1400

SUM 和 AVG 必须用于计算数值数据，但是 MAX 和 MIN 可以计算各种数据类型，比如日期和字符串，注意字符串的大小比较是通过比较 ASCII 标准转换结果实现的

SQL> --计算各个部门最早和最晚入职员工的入职日期
SQL> SELECT e.deptno
  2        ,MAX(e.hiredate) last_hiredate
  3        ,MIN(e.hiredate) first_hiredate
  4    FROM emp e
  5   GROUP BY e.deptno;


DEPTNO LAST_HIREDATE FIRST_HIREDATE
------ ------------- --------------
    30 1981/12/3     1981/2/20
    20 1983/1/12     1980/12/17
    10 1982/1/23     1981/6/9

值得注意的是，聚合函数总是忽略列中的空值，如果分组内指定列没有可以用于计算的非空值（全部是空值，或者甚至组内没有行），则诸如 SUM、MAX、MIN、AVG 等聚合运算函数返回 NULL

SQL> --聚合函数总是避开空值（注意COMM列中的NULL值）
SQL> SELECT e.deptno, e.comm FROM emp e;


DEPTNO      COMM
------ ---------
    20 
    30    300.00
    30    500.00
    20 
    30   1400.00
    30 
    10 
    20 
    10 
    30      0.00
    20 
    30 
    20 
    10


SQL> SELECT e.deptno, MAX(e.comm) max_comm, MIN(e.comm) min_comm, SUM(e.comm) sum_comm
  2    FROM emp e
  3   GROUP BY e.deptno;


DEPTNO   MAX_COMM   MIN_COMM   SUM_COMM
------ ---------- ---------- ----------
    30       1400          0       2200
    20                       
    10

COUNT 函数用于计数，返回本分组内列中非空值的个数，注意它也是忽略空值的，但如果分组内指定列没有可以用于计数的非空值，COUNT 函数返回 0

SQL> --COUNT函数是忽略空值而计数的
SQL> SELECT e.deptno
  2        ,COUNT(e.comm) not_null_comm_count
  3        ,COUNT(e.empno) emp_count
  4    FROM emp e
  5   GROUP BY e.deptno;


DEPTNO NOT_NULL_COMM_COUNT  EMP_COUNT
------ ------------------- ----------
    30                   4          6
    20                   0          5
    10                   0          3


SQL> --即便作用于没有行的分组，聚合函数仍然坚持返回结果
SQL> SELECT MAX(1), MIN(1), SUM(1), AVG(1), COUNT(1) FROM dual WHERE 1 = 0;


    MAX(1)     MIN(1)     SUM(1)     AVG(1)   COUNT(1)
---------- ---------- ---------- ---------- ----------
                                                     0

COUNT 函数常用于计数各个分组内的行数，这时需要注意使用非空的列或者定义伪列更为可靠

特别强调，COUNT(*) 并不意味着将表中全部列纳入计数范围，符号 * 在这里不代表全部列，故不会忽略全部自动都为空的列

SQL> --COUNT(1)与COUNT(col)
SQL> SELECT e.deptno, COUNT(e.comm) not_null_comm_count, COUNT(1) emp_count
  2    FROM emp e
  3   GROUP BY e.deptno;


DEPTNO NOT_NULL_COMM_COUNT  EMP_COUNT
------ ------------------- ----------
    30                   4          6
    20                   0          5
    10                   0          3


SQL> --COUNT(*)与COUNT(col)
SQL> SELECT e.deptno, COUNT(e.comm) not_null_comm_count, COUNT(*) emp_count
  2    FROM emp e
  3   GROUP BY e.deptno;


DEPTNO NOT_NULL_COMM_COUNT  EMP_COUNT
------ ------------------- ----------
    30                   4          6
    20                   0          5
    10                   0          3


SQL> --注意表中有一行为“空”行
SQL> SELECT * FROM cux_count_star_test;


        ID
----------
       100


       300


SQL> SELECT COUNT(1) count_1, COUNT(id) count_col, COUNT(*) count_star
  2    FROM cux_count_star_test;


   COUNT_1  COUNT_COL COUNT_STAR
---------- ---------- ----------
         3          2          3

HAVING 条件用于对分组后的结果进行进一步的筛选限制

SQL> --计算平均工资高于2000的部门的最高工资、最低工资
SQL> SELECT e.deptno, MAX(e.sal) max_sal, MIN(e.sal) min_sal
  2    FROM emp e
  3   GROUP BY e.deptno
  4  HAVING AVG(e.sal) > 2000;


DEPTNO    MAX_SAL    MIN_SAL
------ ---------- ----------
    20       3000        800
    10       5000       1300

简述数据分组实现原理

以上面的 SQL 为例，数据库在执行数据分组 SQL 时，其实是历经了两大主要过程：

一开始是排除 GROUP BY [HAVING] 条件，根据 WHERE 条件等限定原始数据的选取范围
然后，特别需要强调的一步是，根据 GROUP BY 列表和参与聚合运算的列形成一个虚拟的数据集，并在这个集中进行分组，最后才在各组内进行指定的聚合运算

所以，基于以上的分组聚合实现原理，我们可以总结一条规律：SELECT 中引用的列，应当要么是分组的依据列，要么是参与聚合运算的列，否则除此以外的列会因为没有被选入中间的虚拟表而不能在最终输出结果集时被找到，从而报错 ORA-00979

SQL> SELECT deptno, ename, SUM(sal) FROM emp GROUP BY deptno;
SELECT deptno, ename, SUM(sal) FROM emp GROUP BY deptno
               *


ORA-00979: 不是 GROUP BY 表达式

需要说明的是，聚合函数与数据分组并不是必须同时使用的：数据分组可以不引用聚合函数，使用聚合函数也可以不进行数据分组（或者说此时视表中全部行为一个组）

SQL> --数据分组实现DISTINCT去重效果
SQL> SELECT deptno, job FROM emp GROUP BY deptno, job;


DEPTNO JOB
------ ---------
    30 CLERK
    10 CLERK
    20 ANALYST
    30 SALESMAN
    20 CLERK
    10 MANAGER
    10 PRESIDENT
    20 MANAGER
    30 MANAGER


SQL> --统计表中总共有多少行
SQL> SELECT COUNT(1) FROM emp;


  COUNT(1)
----------
        14

今天的课程就讲到这里，下节课我们学习多张表联合查询

oracle

文章转载自SQL干货分享，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle PL/SQL 基础教程（4）复杂查询（一）

评论