oracle-分组统计查询和子查询

范例：统计所有雇员的人数，支付的总工资，平均工资，最高工资，最低工资

select count(empno),sum(sal),avg(sal),max(sal),min(sal) from emp;

范例：统计带工薪的总工资和平均工资

select sum(sal+nvl(comm,0)*12) ,avg(sal+nvl(comm,0)*12) from emp;

面试题：请解释count(*) count(字段)count（distinct 字段）的区别？

1. count（*）：明确返回表中数据的条数
2. count（字段）：不统计为null的数据条数，如果不为null，那么就等于count（*）
3. count（distinct 字段）：统计消除重复数据后的数据条数

在实现具体操作之前必须要先解决一个问题，什么情况下可以分组？

         ·例如：全班，男生一组，女生一组，互相拔河

         ·例如：全部，戴眼镜一组，不戴眼镜一组，互相比裸眼视力

         ·例如：25岁之前一组，25岁之后一组

         实际上所谓的分组，指的是某些群体具备共同的特征。现在回到emp表中，可以发现如果存在有重复的数据，那么就证明这两个字段上都可以进行分组。

         但是一定要记住，实际上一条记录也可以进行分组。只不过这类的做法是没有意义的。可以采用如下语法完成分组。

Select [distinct] * |列名称 [别名],列名称 [别名],…|统计函数 From 数据表 [别名]，数据表 [别名],… [where 条件(s)] [group by 分组字段，分组字段,…] [order by 字段 [ASC|DESC]];

范例：按照职位进行分组，统计出每个职位的平均工资、最高工资和最低工资、人数。

select job ,avg(sal),max(sal),min(sal),count(empno) from emp group by job; 1、group by 和第一个字段相同； 2、后边的都是统计函数否则会出错；

范例：按照部门编号进行分组，统计出每个部门的人数，平均工资，平均服务年限

select deptno ,count(empno),avg(sal),trunc(avg((sysdate-hiredate)/12)) from emp group by deptno;

使用统计函数的限制：

1. 统计函数单独使用时，没有group by子句，只能够出现统计函数；
2. 使用统计函数时，存在group by子句，select 子句只能够出现分组字段，其他任何字段字段都不能出现；
3. 统计函数在分组之中可以嵌套使用，但是嵌套之后的统计查询之中不能出现任何字段，包括分组字段；

以上的操作都是针对于单张表一个字段的分组，那么通过之前给出的语法可以设置多个分组字段，那么就要求这多个字段必须同时重复。

范例：查询出每个部门的名称，部门人数和平均工资。

         |- 确立使用的数据表：

                  dept表：找到部门名称；

                  emp表：统计部门人数、平均工资。

         |- 确立已知的关联字段

                  雇员和部门关联：emp.deptno=dept.deptno;

1、先不考虑分组的问题，换个角度，实现查询；查询出每个部门的名称，雇员编号，平均工资

select e.empno,d.dname,sal from emp e,dept d where e.deptno = d.deptno;

2、在以上查询的过程中，发现了部门的名称数据是重复的，但是这个时候并不是对原始表进行操作，而是对这个临时表进行操作。

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.dname,count(e.empno),avg(e.sal) from emp e,dept d where e.deptno(+)=d.deptno group by d.dname;

范例：查询出每个部门的编号，名称，位置，部门人数，平均工资

         |- 确定所需要的数据表

1. emp表：找到部门编号，名称，位置
2. dept表：统计部门人数，平均工资

|- 确定已知的关联字段

         雇员和部门关联：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;

此时，最重要的是发现了临时表里边有deptno，loc，dname数据存在重复，并且想要对部门人数和平均工资使用统计函数，那么就用三个分组字段，并且使用右外连接

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;

错误：

select job ,avg(sal) from emp where avg(sal)>2000  group by job;

原因：

where子句之中不能够使用统计函数，where是在group by之间执行的，而此时的条件明显是在group by之后执行，所以使用having子句完成。

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

sql语句执行顺序：

1. from子句确定数据来源
2. where子句过滤数据行
3. group子句执行分组操作
4. having子句针对于分组之后进行数据过滤
5. select子句确定数据列
6. orderby子句进行数据排序

二、子查询

下面情况适用于大部分子查询：

1. 子查询返回单行单列：having、where
2. 子查询返回单行多列：where
3. 子查询返回多行多列：from

范例：子查询返回单行单列è要求统计出所有高于公司平均工资的全部雇员信息

select * from emp where emp.sal>(select avg(sal) from emp);

范例：子查询返回多行多列è要求找到与SCOTT工资相同，职位相同的雇员信息

select sal,job from emp where (sal,job)=(select sal,job from emp where emp.ename='SCOTT'); 注意：此时‘字符串’是区分大小写的，找不到小写字母的名字

IN：指的是在指定范围之内查询è

select * from emp where sal in(select sal from emp where job='MANAGER');

使用not in的时候必须注意此时不能包含null，否则会进行全选。

·>ANY：比子查询的最小值要大；

·<ANY：比子查询的最大值要小；

·>ALL：比子查询的最大值要大；

·<ALL：比子查询的最小值要小       ；

在having子句中使用子查询意味着进行了分组查询。

范例：查询高于公司平均工资的部门编号和平均工资

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

范例：查询出平均工资最低的职位信息、人数和平均工资

select job,avg(sal) from emp group by job having avg(sal) =(select min(avg(sal)) from emp group by job); select job,count(empno),avg(sal) from emp where sal=(select min(avg(sal)) from emp group by job ) group by job;

在from子句之中使用子查询。from子句主要功能是确定数据来源，而且数据来源是数据表，表是一种行和列的集合。from子句里边出现的子查询，其返回的结果一定是多行多列数据。

范例：查询出每个部门的编号，名称，位置，部门人数，平均工资

         |- 确定表：

1. emp表：部门的人数，平均工资
2. dept表：部门的编号，名称，位置

|- 关联的字段：

         emp.deptno = dept.deptno;

select d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal) from emp e,dept d group by d.deptno,d.dname,d.loc;

         通过以上的程序虽然实现了与之前多个字段分组相同的功能，但是代码实在是过于复杂了。形成的笛卡尔积很大，造成了性能的下降，所以使用子查询。

 

范例：要求查询出高于公司平均工资的雇员姓名，职位，工资，领导姓名、部门名称，部门人数，部门平均工资，工资等级的雇员人数。

         |-确定要使用的数据表

1. emp表：统计公司的平均工资
2. emp表：雇员姓名、职位、工资、领导姓名
3. dept表：部门的名称
4. emp表：部门的人数、平均工资
5. salgrade表：工资等级
6. salgrade表：统计一个工资等级的人数

|- 确立已知的关联字段：

1. emp.mgr = emp2.mgr
2. emp.deptno = dept.deptno;
3. emp.sal between salgrade.losal and hisal;

select e.ename 雇员姓名,e.job 雇员职位，e.sal 雇员工资,m.ename 领导姓名,m.job 领导职位 from emp e,emp m where e.sal>(select avg(sal) from emp) and e.mgr=m.empno(+);

加入dept表，找到部门名称

select e.ename 雇员姓名,e.job 雇员职位，e.sal 雇员工资,m.ename 领导姓名,m.job 领导职位,d.dname 部门名称 from emp e,emp m ,dept d where e.sal>(select avg(sal) from emp) and e.mgr=m.empno(+) and e.deptno = d.deptno;

select e.ename 雇员姓名,e.job 雇员职位，e.sal 雇员工资,m.ename 领导姓名,m.job 领导职位,d.dname 部门名称,dtemp.count 部门人数 ,s.grade 工资等级 from emp e,emp m ,dept d,(select deptno dno,count(empno) count from emp group by deptno) dtemp ,salgrade s where e.sal>(select avg(sal) from emp) and e.mgr=m.empno(+) and e.deptno = d.deptno and dtemp.dno=d.deptno and e.sal between s.losal and s.hisal;