Oracle学习(四)：组函数

SQL> --在select列表中所有未包含在组函数中的列都应该包含在group by子句中

SQL> --包含在group by子句中的列不必包含在select列表中

SQL> --按部门，不同的职位统计平均工资

SQL> select deptno,job,avg(sal)

2 from emp

3 group by deptno,job

4 order by 1

SQL> --group by多列：先按照第一列分；如果第一列相同，再按照第二列分

SQL> --having：过滤分组

SQL> --查询平均工资大于2000的部门

SQL>select deptno,avg(sal)

2 from emp

3 group by deptno

4 having avg(sal)>2000

SQL> --having和where的区别

SQL> --求10号部门的平均工资

SQL> select deptno,avg(sal)

2 from emp

3 group by deptno

4 having deptno=10;

SQL> --SQL优化 3. 尽量使用where，例外：如果条件中含有组函数，只能使用having

SQL> --group by的增强：做部门报表可能用到

SQL> – group by deptno,job + group by deptno + group by null = group by rollup(deptno,job)

SQL> – group by rollup(a,b) = group by a,b + group by a + group by null

SQL> select deptno,job,sum(sal)

2 from emp

3 group by rollup(deptno,job);

SQL> --SQLPLUS支持报表功能

SQL> break on deptno skip 2 --break on deptno：按照部门号分段，相同的部门号只显示一次；skip 2：不同的部门之间空2行

2.在Sqlplus下实际执行的结果录屏：

SQL> host cls

SQL> --工资总额

SQL> select sum(sal) from emp;

SUM(SAL)

---

29025

SQL> --员工人数

SQL> select count(*) from emp;

COUNT(*)

---

14

SQL> --平均工资

SQL> select sum(sal)/count(*) 一, avg(sal) 二 from emp;

一 二

---

2073.21429 2073.21429

SQL> --平均奖金

SQL> select sum(comm)/count(*) 一, sum(comm)/count(comm) 二, avg(comm) 三 from emp;

一 二 三

---

157.142857 550 550

SQL> --空值 4. 组函数会自动滤空

SQL> select count(*),count(comm) from emp;

COUNT(*) COUNT(COMM)

---

14 4

SQL> select count(*),count(nvl(comm,0)) from emp;

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

---

14 14

SQL> --可以在组函数中嵌套滤空函数，来屏蔽组函数的滤空功能

SQL> host cls

SQL> --求各个部门的平均工资

SQL> set linesize 150

SQL> col sal for 9999

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---

7369 SMITH CLERK 7902 17-12月-80 800 20

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7788 SCOTT ANALYST 7566 13-7月 -87 3000 20

7839 KING PRESIDENT 17-11月-81 5000 10

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

7876 ADAMS CLERK 7788 13-7月 -87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---

7900 JAMES CLERK 7698 03-12月-81 950 30

7902 FORD ANALYST 7566 03-12月-81 3000 20

7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择14行。

SQL> select deptno,avg(sal)

2 from emp

3 group by depnto;

group by depnto

第 3 行出现错误:

ORA-00904: “DEPNTO”: 标识符无效

SQL> ed

已写入 file afiedt.buf

1 select deptno,avg(sal)

2 from emp

3* group by deptno

SQL> /

DEPTNO AVG(SAL)

---

30 1566.66667

20 2175

10 2916.66667

SQL> --语法

SQL> select deptno,job,avg(sal)

2 from emp

3 group by depnto;

group by depnto

第 3 行出现错误:

ORA-00904: “DEPNTO”: 标识符无效

SQL> ed

已写入 file afiedt.buf

1 select deptno,job,avg(sal)

2 from emp

3* group by deptno

SQL> /

select deptno,job,avg(sal)

第 1 行出现错误:

ORA-00979: 不是 GROUP BY 表达式

SQL> ed

已写入 file afiedt.buf

1 select deptno,job,avg(sal)

2 from emp

3* group by deptno,job

SQL> --group by多列：先按照第一列分；如果第一列相同，再按照第二列分

SQL> ed

已写入 file afiedt.buf

1 select deptno,job,avg(sal)

2 from emp

3 group by deptno,job

4* order by 1

SQL> /

DEPTNO JOB AVG(SAL)

---

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

20 ANALYST 3000

20 CLERK 950

20 MANAGER 2975

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 1400

已选择9行。

SQL> --按部门，不同的职位统计平均工资

SQL> host cls

SQL> --having

SQL> select deptno,avg(sal)

2 from emp

3 group by depnto;

group by depnto

第 3 行出现错误:

ORA-00904: “DEPNTO”: 标识符无效

SQL> ed

已写入 file afiedt.buf

1 select deptno,avg(sal)

2 from emp

3* group by deptno

SQL> /

DEPTNO AVG(SAL)

---

30 1566.66667

20 2175

10 2916.66667

SQL> --查询平均工资大于2000的部门

SQL> ed

已写入 file afiedt.buf

1 select deptno,avg(sal)

2 from emp

3 group by deptno

4* having avg(sal)>2000

SQL> /

DEPTNO AVG(SAL)

---

20 2175

10 2916.66667

SQL> --having 过滤分组

SQL> host cls

SQL> --having和where的区别

最后

这份《“java高分面试指南”-25分类227页1000+题50w+字解析》同样可分享给有需要的朋友，感兴趣的伙伴们可挑战一下自我，在不看答案解析的情况，测试测试自己的解题水平，这样也能达到事半功倍的效果！（好东西要大家一起看才香）

10 2916.66667

SQL> --having 过滤分组

SQL> host cls

SQL> --having和where的区别

最后

这份《“java高分面试指南”-25分类227页1000+题50w+字解析》同样可分享给有需要的朋友，感兴趣的伙伴们可挑战一下自我，在不看答案解析的情况，测试测试自己的解题水平，这样也能达到事半功倍的效果！（好东西要大家一起看才香）

[外链图片转存中…(img-whMEtaRh-1714435402761)]

[外链图片转存中…(img-KnXI1O9v-1714435402762)]

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录