Oracle数据库——分组查询

select * from emp;

开始之前需要导入一些表，下载地址：https://yuyunyaohui.lanzous.com/ivPKcdnlecd
导入方法：https://blog.csdn.net/hyh17808770899/article/details/106744978

示例数据：员工表emp、部门表dept、工资级别表salgrade

一、分组函数的概念

什么是分组函数？
分组函数作用于一组数据，并对一组数据返回一个值。

二、分组函数的使用

SELECT     [column,] group function(column),…
FROM 	    table
[WHERE   condition]
[GROUP BY   column]
[ORDER BY   column]

---

三、常用的分组函数

1、AVG（平均值）和SUM（合计）函数

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

select avg(sal),sum(sal) from emp;

2、MIN（最小值）和MAX（最大值）函数

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

select min(sal),max(sal) from emp;

3、COUNT（计数）函数

求出员工的总人数

select count(*) from emp;
select count(empno) from emp;

4、DISTINCT（distinct）关键字

求出部门数

select count(distinct deptno) from emp;

5、LISTAGG：行转列

set linesize 200;
col 部门中员工的姓名 for a60;
select deptno 部门号,listagg(ename,',') within group (order by ename) 部门中员工的姓名
from emp group by deptno;

6、分组函数与空值

分组函数会自动忽略空值

例1：统计员工的平均工资

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

例2：统计员工的平均奖金

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

7、在分组函数中使用NVL函数

注意：NVL函数使分组函数无法忽略空值

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

使用GROUP BY 子句数据分组

---

三、分组数据

GROUP BY 子句语法

SELECT     column, group function(column),…
FROM 	    table
[WHERE   condition]
[GROUP BY   group _by_expression]
[ORDER BY   column];

1、可以使用GROUP BY子句将表中的数据分成若干组

（1）在select列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中

例：求每个部门的平均工资，要求显示：部门号，部门平均工资

select deptno,avg(sal)
from emp
group by deptno;

SELECT     a,b,c,组函数（x）
FROM 	 table 
GROUP BY   a,b,c;

（2）包含在GROUP BY子句中的列不必包含在select列表中

例：求每个部门的平均工资，要求显示：部门平均工资

select avg(sal)
from emp
group by deptno;

SELECT    组函数（x）
FROM 	 table 
GROUP BY   a,b,c;

2、使用多个列分组

例：按部门、不同的职位，统计员工的工资总额

select deptno,empjob,sum(sal)
from emp
group by deptno,empjob
order by deptno;

3、过滤分组

having子句的使用

having子句的语法

SELECT         column, group_function(column),…
FROM 	        table
[WHERE        condition]
[GROUP BY   group_by_expression]
[HAVING  	  group_condition]
[ORDER BY   column];

having子句示例
求平均工资大于2000的部门，要求显示：部门号，平均工资

select deptno,avg(sal)
from emp
group by deptno
having avg(sal) > 2000;

---

四、使用HAVING子句过滤分组结果集

1、where与having的区别

不能再WHERE子句中使用组函数（注意）

可以在HAVING子句中使用组函数

2、where与having可以通用的情况

例：查询10号部门的平均工资
注：从SQL优化的角度上看，尽量使用where；
having先分组，后过滤；where先过滤，后分组

select deptno,avg(sal)
from emp
group by deptno
having deptno=10;

select deptno,avg(sal)
from emp
where deptno=10
group by deptno;

---

五、在分组查询中使用order by子句

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

可以按照：（列、别名、表达式、序号）进行排序

select deptno,avg(sal)
from emp
group by deptno
order by avg(sal);

select deptno,avg(sal) 平均工资
from emp
group by deptno
order by 平均工资;

select deptno,avg(sal) 平均工资
from emp
group by deptno
order by 2;

1、分组函数的嵌套

例：求部门平均工资的最大值
1、通过AVG函数求出每个部门的平均工资
2、嵌套MAX函数求出部门平均工资的最大值

select max(avg(sal))
from emp
group by deptno;

2、group by 语句的增强

（1）按部门、不同的职位，统计工资总额

select deptno,empjob,sum(sal)
from emp
group by deptno,empjob
order by deptno;

（2）按部门，统计工资总额

select deptno,avg(sal)
from emp
group by deptno
order by deptno;

（3）统计工资总额

select sum(sal) from emp;

语法：

	group by rollup(a, b)
等价于：
	group by a,b
	+
	group by a
	+
	group by null

select deptno,empjob,sum(sal)
from emp
group by rollup(deptno,empjob);

break on deptno skip 2;
select deptno,empjob,sum(sal)
from emp
group by rollup(deptno,empjob);

set pagesize 30;
select deptno,empjob,sum(sal)
from emp
group by rollup(deptno,empjob);

---

六、SQL*Plus的报表功能

报表包括：标题、页码、别名等。

ttitle col 15 ‘我的报表’ col 35 sql.pno col 15 表示空15列 sql.pno表示报表的页码

col deptno heading 部门号 col job heading 职位 col sum(sal) heading 工资总额
break on deptno skip 1

get E:\study\大三下\数据库应用开发\导出.sql
ttitle col 15 '我的报表' col 35 sql.pno
col deptno heading 部门号
col empjob heading 职位
col sum(sal) heading 工资总额
break on deptno skip 1;
@E:\study\大三下\数据库应用开发\导出.sql;
select deptno, empjob, sum (sal) ;
from emp
group by rollup (deptno, empjob) ;

set pagesize 10;
select deptno,empjob,sum(sal)
from emp
group by rollup(deptno,empjob);