03-Oracle分组统计

 

此时结果中一共返回了四张数据表。但是要想知道每一张表的结构，则可以使用“DESC 表名称”，例如：要想知道dept表的结构，使用“DESC dept”。

 

1、部门表：dept

 

No.	列名称	类型	描述
1	DEPTNO	NUMBER(2)	部门编号，最多由2位数字所组成
2	DNAME	VARCHAR2(14)	部门名称，由14位字符所组成
3	LOC	VARCHAR2(13)	部门位置

 

 

 

2、雇员表：emp（一个部门会存在有多个雇员）

 

No.	列名称	类型	描述
1	EMPNO	NUMBER(4)	雇员编号，最多只能够包含4位数字
2	ENAME	VARCHAR2(10)	雇员姓名
3	JOB	VARCHAR2(9)	职位
4	MGR	NUMBER(4)	领导编号，领导也属于雇员
5	HIREDATE	DATE	雇佣日期
6	SAL	NUMBER(7,2)	基本工资，小数位最多是2位，整数5位
7	COMM	NUMBER(7,2)	佣金，销售人员才具备佣金
8	DEPTNO	NUMBER(2)	所属的部门编号

 

 

 

 

 

3、工资等级表（salgrade）

 

No.	列名称	类型	描述
1	GRADE	NUMBER	工资等级编号
2	LOSAL	NUMBER	此等级的最低工资
3	HISAL	NUMBER	此等级的最高工资

 

 

 

 

 

4、工资表（bonus）

 

No.	列名称	类型	描述
1	ENAME	VARCHAR2(10)	雇员姓名
2	JOB	VARCHAR2(9)	雇员职位
3	SAL	NUMBER	工资
4	COMM	NUMBER	佣金

 

       在bonus表中现在没有任何的数据。

 

 

 

 

 

 

 

1、消除笛卡尔积

 

select * from emp,dept

 

where emp.deptno=dept.deptno;

 

 

 

使用别名

 

select e.*,d.dname from emp e,dept d

 

where e.deptno=d.deptno;

 

 

 

加入工资等级信息，与原始的消除笛卡尔积条件应该同时满足，所以使用AND连接

 

select e.empno,e.ename,e.sal,e.job,d.dname,s.grade

 

from emp e,dept d,salgrade s

 

where e.deptno=d.deptno and e.sal between s.losal and s.hisal;

 

 

 

 

 

2、内连接实现效果

 

增加一个没有部门的雇员

 

INSERT INTO emp (empno,ename,deptno) VALUES (9999,'HELLO',null) ;

 

内连接实现效果

 

select e.empno,e.ename,d.deptno,d.dname

 

from emp e,dept d

 

where e.deptno=d.deptno;

 

此时没有部门的雇员以及没有雇员的部门信息都没有出现，因为null的判断不满足, 使用左（外）连接，希望所有的雇员信息都显示出来，即便他没有对应的部门

 

select e.empno,e.ename,d.deptno,d.dname

 

from emp e,dept d

 

where e.deptno=d.deptno(+);

 

此时没有部门的雇员出现了，也就是说左表的数据全部显示了, 使用右（外）连接，将所有的部门信息都显示出来

 

select e.empno,e.ename,d.deptno,d.dname

 

from emp e,dept d

 

where e.deptno(+)=d.deptno;

 

 

 

在之前学习过一个COUNT()函数，这个函数的主要作用是统计一张数据表之中的数据量的个数。但是与它功能类似的常用函数一共有五个：

 

              · 统计个数：COUNT()，根据表中的实际数据量返回结果；

 

              · 求和：SUM()，是针对于数字的统计；

 

              · 平均值：AVG()，是针对于数字的统计；

 

              · 最小值：MIN()，各种数据类型都支持；

 

              · 最大值：MAX()，各种数据类型都支持。

 

3验证各个函数

 

select count(*) 人数,avg(sal) 平均工资,min(sal) 最低工资,

 

     max(sal) 最高工资,sum(sal) 每月总支出

 

from emp;

 

 

 

4统计出公司的平均雇佣年限

 

select avg(months_between(sysdate,hiredate)/12) from emp;

 

 

 

5求出最早和最晚的雇佣日期

 

select min(hiredate) 最早,max(hiredate)最晚 from emp;

 

以上的几个操作函数，在表中没有数据的时候，只有COUNT()函数会返回结果，其他都是null。

 

 

 

面试题：请解释COUNT(*)、COUNT(字段)、COUNT(DISTINCT 字段)的区别？

 

         实际上针对于COUNT()函数有三种使用形式：

 

                   · COUNT(*)：可以准确的返回表中的全部记录数；

 

                   · COUNT(字段)：统计不为null的所有数据量；

 

                   · COUNT(DISTINCT 字段)：消除重复数据之后的结果。

 

 

 

6根据部门编号分组，查询出每个部门的编号、人数、平均工资

 

如果要进行分组则应该使用GROUP BY子句完成

 

【④确定要显示的数据列】SELECT [DISTINCT] * | 分组列 [别名] , 分组列 [别名] ...

 

【①确定数据来源（行与列的集合）】FROM 表名称 [别名], 表名称 [别名],...

 

【②针对于数据行进行筛选】[WHERE 限定条件(s)]

 

【③针对于筛选的行分组】[GROUP BY 分组字段,分组字段,分组字段,...]

 

【⑤对选定数据的行与列排序】[ORDER BY  排序字段 [ASC | DESC], 排序字段 [ASC | DESC],...]

 

select deptno,count(*),avg(sal)

 

from emp

 

group by deptno;

 

 

 

7根据职位分组，统计出每个职位的人数，最低工资与最高工资

 

select job,count(*) 总人数,min(sal) 最低工资,max(sal) 最高工资

 

      from emp

 

group by job;

 

 

 

 

 

实际上GROUP BY子句之所以使用麻烦，是因为分组的时候有一些约定条件：

 

                   · 如果查询不使用GROUP BY子句，那么SELECT子句中只允许出现统计函数，其他任何字段不允许出现。

 

错误的代码：	正确的代码：
SELECT empno,COUNT(*) FROM emp        * 第 1 行出现错误: ORA-00937: 不是单组分组函数	SELECT COUNT(*) FROM emp ;

 

                   · 如果查询中使用了GROUP BY子句，那么SELECT子句中只允许出现分组字段、统计函数，其他任何字段都不允许出现。

 

错误的代码：	正确的代码：
SELECT ename,job,COUNT(*) FROM emp GROUP BY job        * 第 1 行出现错误: ORA-00979: 不是 GROUP BY 表达式	SELECT job,COUNT(*) FROM emp GROUP BY job ;

 

                   · 统计函数允许嵌套，但是嵌套之后的SELECT子句里面只允许出现嵌套函数，而不允许出现任何字段，包括分组字段。

 

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

 

         整个分组统计就以上的三点对于初学者很麻烦。

 

 

 

 

 

8查询出每个部门的名称、部门人数、平均工资

 

select d.dname,count(e.empno) 人数,avg(e.sal)

 

from emp e,dept d

 

where e.deptno(+)=d.deptno

 

group by d.dname;

 

 

 

 

 

9查询每个部门的编号、名称、位置、部门人数、平均工资

 

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;

 

 

 

 

 

10现在要求查询出每个职位的名称，职位的平均工资，但是要求显示的职位的平均工资高于2000。

 

使用GROUP BY:WHERE子句上不允许出现统计函数（分组函数）。因为GROUP BY子句是在WHERE子句之后执行的，那么此时执行WHERE子句时还没有进行分组了，那么就自然无法进行统计。所以在这样的情况下，就必须使用另外一个子句完成：HAVING子句，而此时的SQL语法结构变为如下形式：

 

【⑤确定要显示的数据列】SELECT [DISTINCT] * | 分组列 [别名] , 分组列 [别名] ... 【①确定数据来源（行与列的集合）】FROM 表名称 [别名], 表名称 [别名],... 【②针对于数据行进行筛选】[WHERE 限定条件(s)] 【③针对于筛选的行分组】[GROUP BY 分组字段,分组字段,分组字段,...] 【④针对于筛选的行分组】[HAVING 分组过滤] 【⑥对选定数据的行与列排序】[ORDER BY  排序字段 [ASC | DESC], 排序字段 [ASC | DESC],...]

 

HAVING是在GROUP BY分组之后才执行的筛选，在HAVING里面可以直接使用统计函数

 

关于WHERE与HAVING的区别？

 

         · WHERE子句是在GROUP BY分组之前进行筛选，指的是选出那些可以参与分组的数据，并且WHERE子句之中不允许使用统计函数；

 

         · HAVING子句是在GROUP BY分组之后执行的，可以使用统计函数。

 

 

 

select job,avg(sal) from emp

 

group by job

 

having avg(sal)>2000;

 

 

 

下面通过两个具体的程序来进行分组统计的操作总结。

 

范例：显示所有非销售人员的工作名称以及从事同一工作的雇员的月工资的总和，并且要求满足从事同一工作雇员的月工资的合计大于5000，显示的结果按照月工资的合计的升序排列。

 

第一步：查询所有非销售人员的信息，WHERE进行限定查询。

 

select * from emp where job<>'SALESMAN';

 

 

 

第二步：按照职位进行分组，而后求出月工资的总支出；

 

select job,sum(sal) from emp

 

where job<>'SALESMAN'

 

group by job;

 

 

 

第三步：分组后的数据进行再次的筛选，使用HAVING子句

 

select job,sum(sal) from emp

 

where job<>'SALESMAN'

 

group by job

 

having sum(sal)>5000;

 

 

 

第四步：按照月工资的合计升序排列，使用ORDER BY。

 

select job,sum(sal) 合计 from emp

 

where job<>'SALESMAN'

 

group by job

 

having sum(sal)>5000

 

order by 合计;

 

 

 

 

 

11统计所有领取佣金和不领取佣金的人数、平均工资

 

select comm,count(*),avg(sal)

 

from emp

 

group by comm;

 

 

 

查询出所有领取佣金的雇员的人数、平均工资。直接使用WHERE子句

 

select '领取佣金'info ,count(*),avg(sal)

 

from emp

 

where comm is not null;

 

 

 

查询所有不领取佣金的雇员的人数、平均工资。直接使用WHERE子句

 

select '不领取佣金'info,count(*),avg(sal)

 

from emp

 

where comm is not null;

 

 

 

以上sql语句进行链接

 

select '领取佣金'info,count(*),avg(sal)

 

from emp

 

where comm is not null

 

    union

 

select '不领取佣金'info,count(*),avg(sal)

 

from emp

 

where comm in null;