Oracle 中关于 Group By 子句与多行函数嵌套搭配使用的注意事项
2020-01-05 11:32 askscuti 阅读(2247) 评论(0) 编辑 收藏 举报你需要知道的
提到 Group by 子句，你需要先理解一个东西：函数的分类。提到函数分类，你脑海里面需要瞬间想到Oracle中的函数分类：单行函数（Single-row functions）、多行函数（Multiple-row functions）。请把中文英文都背下来，也就这么两个，这是Oracle的函数分类的体系，很重要。以后再遇到这个知识点，你至少能够胸有成竹，张口就来。
啥叫单行函数
举个现炒板栗：你说你要查 EMP 表中，所有人员的名字和他对应的工资，于是发出下面的语句并得到结果。
SQL> select ename,sal from emp;
ENAME SAL
-------------------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
14 rows selected.
你会发现，14行结果中，每个人的名字都是大写（注意：Oracle 语句不区分大小写，但是存的值是区分大小写的，SCOTT 和 scott 是两个值）。你说你看的不习惯，问能否把名字处理成“小写”字母来显示？
这里我们给出一个东西 lower()，它是什么？你先不管，先叫它“函数”，我们用它把名字处理一下：发出以下命令并得到结果。
SQL> select lower(ename),sal from emp;
LOWER(ENAME) SAL
-------------------- ----------
smith 800
allen 1600
ward 1250
jones 2975
martin 1250
blake 2850
clark 2450
scott 3000
king 5000
turner 1500
adams 1100
james 950
ford 3000
miller 1300
14 rows selected.
你会发现，名字通过 lower 函数处理之后，全部变为了小写，更重要的是，处理完毕后，结果集的行数（14行）和处理前结果集的行数（14行）完全一致，也就是说，用了这个 lower 函数处理数值，最终的结果条目数是一比一的呈现，于是，我们称 lower 这个函数为：单行函数（针对结果集的每一行都返回一个值）。
啥叫多行函数
你说你要查 EMP 表中，所有人员工资的总和，于是发出下面的语句并得到结果。
SQL> select sum(sal) from emp;
SUM(SAL)
----------
29025
这个 sum 是什么，也是个函数，你会发现，使用了这个 sum 求和函数后，最终返回一行数据。原来 14 行，使用了 sum 变为 1 行，于是，我们称 sum 这个函数为：多行函数（针对每个结果集只返回一个值）。不要这么记：针对多行只返回一个值的称作多行函数。
如何理解<每个结果集>这个概念
再看个例子：按照部门，查询每个部门的工资总和，于是发出下面的语句并得到结果。
SQL> select deptno,sum(sal) from emp group by deptno order by 1;
DEPTNO SUM(SAL)
---------- ----------
10 8750
20 10875
30 9400
呈现的结果为3行数据，处理前一共 14 行，通过 sum 求和函数处理后得到 3 行数据，请问：sum 是多行函数吗？当然是！这里涉及到一个基本概念的理解，如果你认为：针对多行返回一行称为多行函数。那么我通过这个例子就可以反驳你，因为我针对多行（14行）返回了 3 行，不是 1 行。你能告诉我说 sum 不是多行函数吗？所以，这种说法本身就存在歧义。
而官方教材对多行函数的精准描述是这样的：Return one result per set of rows.什么意思？针对每个行集（或理解为结果集）返回一个值（就是一行）。这么记，你对多行函数的概念就不会迷迷糊糊了。
那如何理解行集（结果集）这个概念？我们求所有部门的工资总和 sum(sal) ，这个行集就是 14 行数据，最终返回 1 行，就是总额。我们按照部门，求每个部门的工资总和，因此要按照部门分开，求出每个部门的工资总和，我们先看下 10 号部门的行数：
SQL> select count(1) from emp where deptno=10;
COUNT(1)
----------
3
10 号部门有 3 行数据，因此，对于 10 号部门工资求和的行集就是 3 行，通过计算这 3 行的数据，最终返回 1 行，就是 10 号部门的工资总额。
再来看 20 号部门：这个部门一共 5 行数据，因此，对于 20 号部门来说，行集就是 5 行，最终返回 1 行，就是 20 号部门的工资总额。
SQL> select count(1) from emp where deptno=20;
COUNT(1)
----------
5
30 号部门也一样：一共 6 行数据，因此，对于 30 号部门来说，行集就是 6 行，最终返回 1 行，就是 30 号部门的工资总额。
SQL> select count(1) from emp where deptno=30;
COUNT(1)
----------
6
Group by 子句使用规则
- 使用多行函数不一定要使用 group by 子句
例如：查询所有部门工资总和
SQL> select sum(sal) from emp;
- select 子句中包含的所有字段（除使用了多行函数的字段）都必须包含在 group by 子句中
例如：按照部门分组查询每个部门的工资总和
SQL> select deptno,sum(sal) from emp group by deptno;
注意：select 子句中包含两个字段 deptno 和 sum(sal) ，因为 sal 字段使用了多行函数，所以在后面 group by 子句中只需要包含 deptno 即可。
- select 子句所有字段都使用了多行函数，group by 子句可以根据需求指定分组字段
# 以下语句都是正确的。从业务逻辑层面来说，毫无意义，只是为了展示语句规则。
SQL> select sum(sal) from emp group by ename;
SQL> select sum(sal) from emp group by ename,empno;
SQL> select sum(sal) from emp group by ename,empno,sal;
- 多行函数嵌套必须使用 group by 子句
注意下嵌套函数：嵌套函数不是一个函数，而是对函数叠加使用的描述。例如：求出哪个部门工资总和最大。
SQL> select max(sum(sal)) from emp group by deptno;
MAX(SUM(SAL))
-------------
10875
这个 max(sum(sal)) 就是一个嵌套函数，多行函数嵌套最多嵌套 2 层。单行函数嵌套无限制。
- select子句中不允许出现除多行函数嵌套的其他字段
例如：下面语句报错
SQL> select deptno,max(sum(sal)) from emp group by deptno;
select deptno,max(sum(sal)) from emp group by deptno
*
ERROR at line 1:
ORA-00937: not a single-group group function
看一道 071 考题
Which query is valid?
A. SELECT dept_id,join_date,sum(salary) FROM employees GROUP BY dept_id,join_date;
B. SELECT dept_id,join_date,sum(salary) FROM employees GROUP BY dept_id;
C. SELECT dept_id,MAX(AVG(salary)) FROM employees GROUP BY dept_id;
D. SELECT dept_id,AVG(MAX(salary)) FROM employees GROUP BY dept_id;
答案：A
