Oracle数据库的学习记录:

十九、分组统计查询----分组查询
能够分组的时候往往是指的是部分数据具备某些共性
要想分组,使用GROUP BY子句
SELECT [DISTINCT] 分组字段 [别名],分组字段 [别名],...|统计函数
,...FROM 表名称 [别名] [WHERE 过滤条件] [GROUP BY 分组字段,分组字段...] [ORDER BY 字段
[ASC|DESC],...,字段 [ASC|DESC],];
先FROM,再WHERE,再GRUOP BY,再SELECT,最后ORDER BY
范例:要求按照职位分组,统计出每个职位的名称、平均人数、平均工资
SELECT job,COUNT(*),AVG(sal) FROM emp GROUP BY job;
范例:要求查询出每个部门编号,以及每个部门的人数、最高与最低工资
SELECT deptno,COUNT(empno),MAX(sal),MIN(sal) FROM emp GROUP BY deptno;
所选择的job和deptno两个字段上都是存在有重复数据的

对于分组操作严格来讲还是存在一些使用上的限制的,要求如下:
**限制1:在没有编写GROUP BY子句的时候(全表一组),那么SELECT子句之中只允许出现统计函数,不允许出现任何的其他字段
Wrong:
SELECT COUNT(empno),ename FROM emp ;
**限制2:在使用GROUP BY子句分组的时候,SELECT子句只允许出现分组字段和统计字段,其他字段不允许出现
Wrong:
SELECT job,COUNT(empno),ename FROM emp GROUP BY job;
**限制3:统计函数允许嵌套查询,但是嵌套后的统计查询中,SELECT子句里面不允许出现任何的字段,包括分组字段,只能够使用嵌套的统计函数
Wrong:
SELECT deptno,MAX(AVG(sal)) FROM emp GROUP BY deptno;

范例:查询每个部门的名称、人数、平均工资
**确定要使用的数据表:
  |-dept表:部门名称
  |-emp表:统计出人数、平均工资
**确定已知的关联字段:
  |-雇员与部门:emp.deptno=dept.deptno
第一步:查询每个雇员的编号、工资、部门名称
SELECT e.empno,d.dname,e.sal FROM emp e,dept d WHERE e.deptno=d.deptno;
第二步:通过以上的查询可以发现在dname字段上出现了重复数据,有重复数据才可以分组,另外,明确要求按部门名称分组
SELECT d.dname,COUNT(e.empno),AVG(e.sal) FROM emp e,dept d WHERE
e.deptno(+)=d.deptno GROUP BY d.dname;
以上程序最大的特点是针对于一个多表查询的结果进行的分组,是行列构成,所以可以理解为一张临时数据表

范例:查询出每个部门的编号、名称、位置、部门人数、平均服务年限
第一步:查询出每个部门的编号、名称、位置、雇员编号、雇用日期
SELECT d.deptno,d.dname,d.loc,e.empno,e.hiredate FROM emp e,dept d WHERE
e.deptno(+)=d.deptno;
第二步:三个字段分组
SELECT
d.deptno,d.dname,d.loc,COUNT(e.empno),AVG(MONTHS_BETWEEN(SYSDATE,e.hiredate)/12)
year FROM emp e,dept d WHERE e.deptno(+)=d.deptno GROUP BY
d.deptno,d.dname,d.loc;
多字段分组,只有多个列的数据完全重复的时候才可以使用

范例:要求查询出平均工资高于2000的职位名称以及平均工资
SELECT [DISTINCT] 分组字段 [别名],分组字段 [别名],...|统计函数
,...FROM 表名称 [别名] [WHERE 过滤条件] [GROUP BY 分组字段,分组字段...] [HAVING 分组后的过滤条件]
[ORDER BY 字段 [ASC|DESC],...,字段 [ASC|DESC],];
先FROM,再WHERE,再GROUP BY,再HAVING,再SELECT,最后ORDER
实现分组后的过滤:
SELECT job,AVG(sal) FROM emp GROUP BY job HAVING AVG(sal)>2000;

注意:关于HAVING和WHERE的区别?
**WHERE发生在GROUP BY之前,属于分组前的筛选
**HAVING发生在GROUP BY之后,是针对于分组后的数据进行筛选
WHERE子句不允许使用统计函数,HAVING允许使用统计函数

范例:显示非销售人员工作名称以及从事同一工作雇员的月工资总和,并且要满足从事同一工作的雇员的月工资合计大于5000,输出结果按月工资的合计升序排列
第一步:显示非销售人员
SELECT * FROM emp WHERE job<>'SALESMAN';
第二步:按照工作分组,统计出每个工作的工资总和
SELECT job,SUM(sal) FROM emp WHERE job<>'SALESMAN' GROUP BY job;
第三步:才有工资总和大于5000的才进行显示
SELECT job,SUM(sal) FROM emp WHERE job<>'SALESMAN' GROUP BY job HAVING
SUM(sal)>5000;
第四步:数据按照月工资升序排列
SELECT job,SUM(sal) sum FROM emp WHERE job<>'SALESMAN' GROUP BY job HAVING
SUM(sal)>5000 ORDER BY sum ASC;

思考题:统计公司所有领取佣金与不领取佣金的雇员人数、平均工资
查询1:查询领取佣金的雇员人数和平均工资
SELECT '领取佣金' title,COUNT(empno),AVG(sal) FROM emp WHERE conn IS NOT NULL;
查询2:查询不领取佣金的雇员人数和平均工资
SELECT '不领取佣金' title,COUNT(empno),AVG(sal) FROM emp WHERE conn IS NULL;
以上查询结果结构相同,连接
SELECT '领取佣金' title,COUNT(empno),AVG(sal) FROM emp WHERE conn IS NOT NULL
UNION SELECT '不领取佣金' title,COUNT(empno),AVG(sal) FROM emp WHERE conn IS NULL;
在整个分组里面注意,只有在基数固定的时候才可能分组

友情链接
KaDraw流程图
API参考文档
OK工具箱
云服务器优惠
阿里云优惠券
腾讯云优惠券
华为云优惠券
站点信息
问题反馈
邮箱:[email protected]
QQ群:637538335
关注微信