Oracle数据库——分组统计查询-06

分组统计查询（重点，难点）

• 统计函数（分组函数）

在之前使用过一个COUNT()函数，此函数的功能是统计每张数据表之中的数据量，而这个函数就是统计函数
的一种，在SQL中常用的统计函数一共有5个：COUNT(),AVG(),SUM(),MIN(),MAX();

• 范例：统计公司的总人数，平均工资，每月支付的总工资
• SELECT COUNT(empno),AVG(sal),AVG(sal+NVL(comm,0)),SUM(sal)
• FROM emp;

• 范例：求出公司的最高工资和最低工资
• SELECT MAX(sal),MIN(sal)
• FROM emp;

在Oracle之中所有的函数几乎是可以不分数据类型的

• 范例:求出公司的平均服务年限，最早雇佣的员工的日期，最近一次雇佣员工的日期
• SELECT AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),MIN(hiredate),MAX(hiredate)
• FROM emp;

面试题：请解释COUNT(*),COUNT(字段),COUNT(DISTINCT 字段)的区别？
SELECT COUNT(*)，COUNT(empno),COUNT(comm),COUNT(DISTINCT job)
FROM emp;
COUNT(*):可以准确的统计出表中的数据量
COUNT(字段)：如果此字段上没有null，那么最终的效果与“COUNT(*)”相同，如果存在有null，则null不统计
COUNT(DISTINCT 字段)：重复的数据部统计

• 分组统计

在分组统计操作之前首先解决一个问题：什么情况下可能分组？
公司决定男女分为两队进行吃饭比赛；
现在班级要求按照年龄分组，年龄30以上一组，年龄30以下一组
所以所谓的分组指的是一列数据上存在重复的时候才可能进行的操作。虽然一条数据也可以进行分组，但是
意义不大，在SQL语法之中，如果要实现分组主要依靠GROUP BY子句完成，语法

SELECT [DISTINCT] * | 列名称 [别名],...|统计函数|分组字段  
FROM 表名称 [别名]    
WHERE 限定条件(s)
[GROUP BY 分组字段，分组字段,..]    ==<字段数据有重复
[ORDER BY 排序字段[ASC|DESC],排序字段[ASC|DESC],...];  

范例：按照职位的分组，要求统计出每种职位的人数，最高与最低工资
SELECT job,COUNT(empno),MAX(sal),MIN(sal)
FROM emp
GROUP BY job;

范例：要求按照部门编号分组，统计出每个部门的人数，平均工资，平均服务年限
SELECT deptno,COUNT(empno),AVG(sal),AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12) avg_year
FROM emp
GROUP BY deptno; 

 以上的操作实现了分组操作，但是对于分组而言难的不是语法，而是它的若干项限制
问题一：不使用GROUP BY子句，在SELECT子句之中只能够出现统计函数，其它任何字段不允许出现

正确的代码	错误的代码
SELECT COUNT(empno) FROM emp;	SELECT COUNT(empno),enameFROM emp;

问题二：使用GROUP,在SELECT子句之中只能够出现统计函数与分组字段，其它任何字段不允许出现

正确的代码	错误的代码
SELECT job,COUNT(empno) FROM emp GROUP BY job;	SELECT job,COUNT(empno),ename FROM emp GROUP BY job;

问题三：统计函数允许嵌套，但是嵌套之后统计查询里面不能在出现任何的字段，包括分组字段

正确的代码	错误的代码
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;	SELECT deptno,MAX(AVG(sal)) FROM emp GROUP BY deptno;

以上的所有操作都是针对于一个字段实现的分组统计，那么如果有需要也可以定义多个字段实现分组

范例：要求查询出每个部门的编号，名称，位置，部门人数，平均工资
确定所需要的数据表
dept表：每个部门的编号，名称，位置
emp表：统计
确定已知的关联字段
雇员和部门关联：emp.deptno=dept.deptno
第一步：为了可以发现操作，改变以下查询需求，变为“查询出每个部门的编号，名称，位置，雇员编号，工资”
SELECT d.deptno,d.dname,d.loc,e.empno,e.sal
FROM emp e,dept d
WHERE e.deptno=d.deptno;
第二步：以上返回了一个多行多列的查询结果（表的结构=多行多列），所以可以将以上的结果当作临时表
，不管是临时表还是实体表，只要列上存在有重复字段，那么就可以实现分组
SELECT d.deptno,d.dname,d.loc,COUNT(e.empno),AVG(e.sal)
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno
GROUP BY d.deptno,d.dname,d.loc;
GROUP BY后面可以出现的字段就直接决定了SELECT子句之后可以出现的字段

范例;统计出每个工资等级的等级编号，等级最低工资，等级最高工资，此等级的人数，平均服务年限
确定已知的数据表
salgrade表;grade,losal,hisal;
emp表：统计信息（empno,hiredate）
确定已知的关联字段
雇员和工资等级：emp.sal BETWEEN salgrade.losal AND salgrade.hisal.
第一步：转换一下问题思路“查询每个工资等级，等级最高和等级最低工资范围，雇员编号，雇佣日期”
SELECT s.grade,s.losal,s.hisal,e.empno,e.hiredate
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;
第二步：以上的查询结果是一张临时表，但是不管是临时表还是实体表，那么都可以进行分组，进行分组字段
SELECT s.grade,s.losal,s.hisal,COUNT(e.empno),AVG(MONTHS_BETWEEN(SYSDATE,e.hiredate)/12) AVG_YEAR 
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal
GROUP BY s.grade,s.losal,s.hisal;

范例：统计出每个部门的编号，部门人数，平均工资，同时要求部门的平均工资高于2000
现在唯一已知的条件过滤是WHERE子句，所以开始编写如下代码。
SELECT deptno,COUNT(empno),AVG(sal)
FROM emp
WHERE AVG(sal)>2000  ==<ORA-00934:此处不允许使用分组函数
GROUP BY deptno
这个时候出现了错误，主要的原因是WHERE是在GROUP BY之前执行，只有经过了where筛选后的数据才可以
进行分组操作，那么如果要想完成此类操作，只能利用HAVING子句执行，此时语法如下

SELECT [DISTINCT] * | 列名称 [别名],...|统计函数|分组字段  
FROM 表名称 [别名]    
WHERE 限定条件(s)
[GROUP BY 分组字段，分组字段,..]    ==<字段数据有重复
[HAVING 分组后过滤]
[ORDER BY 排序字段[ASC|DESC],排序字段[ASC|DESC],...];  
总结：关于WHERE与HAVING的区别？
WHERE:是在GROUP BY分组前使用，不能使用统计函数
HAVING:是在GROUP BY分组后使用，能使用统计函数

范例：使用HAVING来解决程序问题
SELECT deptno,COUNT(empno),AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG(sal)>2000;
范例A：显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员
的月工资合计大于$5000，输出结果按月工资的合计升序排列
第一步：确定使用emp表，查询所有非销售人员，使用WHERE子句
SELECT    *
FROM emp
WHERE job<>'SALESMAN';
第二步：按照职位进行分组，而后使用SUM（）函数针对于不同的职位工资计算总和
SELECT job,SUM(sal)sum
FROM emp
WHERE job<>'SALESMAN'
GROUP BY job;
第三步：针对于分组后的数据再次执行过滤
SELECT job,SUM(sal)sum
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;
范例B：统计出所有雇员之中领取佣金和不领取佣金的雇员人数，平均工资
可能针对于comm字段操作，于是可能发生如下的查询
SELECT comm,COUNT(empno),AVG(sal),FROM emp GROUP BY comm;
但是以上的查询发现不能够解决问题，因为每一种佣金的数据都会变成一行统计结果。

下面将问题拆分为两部分
第一步：统计出所有领取佣金的雇员人数，平均工资，不需要分组
SELECT '领取佣金'title, COUNT(empno),AVG(sal)
FROM emp
WHERE comm IS NOT NULL;
第一步：统计出所有不领取佣金的雇员人数，平均工资，不需要分组
SELECT '不领取佣金'title, COUNT(empno),AVG(sal)
FROM emp
WHERE comm IS  NULL;
第三步：两个查询的返回结构完全相同，使用UNION
SELECT '领取佣金'title, COUNT(empno),AVG(sal) FROM emp WHERE comm IS NOT NULL
UNION
SELECT '不领取佣金'title, COUNT(empno),AVG(sal) FROM emp WHERE comm IS  NULL;
所有的查询子句的结构全都讲解完成：FROM<WHERE<GROUP BY<HAVING<SELECT<ORDER BY