Oracle数据库------------------------------分组统计查询（分组查询） - 好文

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;
在整个分组里面注意，只有在基数固定的时候才可能分组

热门工具换一换