Oracle 分组函数和子查询
在讲解分组函数之前，也许有人会问，什么是分组函数？分组函数作用于一组数据，并对一组数据返回一个值。
一、数据分组（分组函数）
——max（最大值），min（最小值），avg（平均值），sum（求和），count（求总数）
问题：如何显示所有员工中最高工资和最低工资？
select max(sal),min(sal) from emp;
问题：最高工资那个人是谁？
错误写法：select ename, sal from emp where sal=max(sal);
正确写法：select ename, sal from emp where sal=(select max(sal) from emp);
如果列里面有一个分组函数，其它的都必须是分组函数，否则就出错, 说ORA-00937：不是GROUP BY 表达式。这是语法规定的问题：如果列里面有一个分组函数，其它的不是分组函数，否则在后面需要group by 不是分组函数。例如
select ename, max(sal) from emp; 这语句执行的时候会报错，说ORA-00937：非单组分组函数。如图：
因为max是分组函数，而ename不是分组函数，所以要想执行不报错，则需要在后面加上group by那个非分组函数ename。（如下图）
select ename, max(sal) from emp group by ename;
但是select min(sal), max(sal) from emp;这句是可以执行的。因为min和max都是分组函数
eg：1.查询最高工资员工的名字，工作岗位
select ename, job, sal from emp e where sal= (select max(sal) from emp);
eg：2.显示工资高于平均工资的员工信息
select * from emp e where sal > (select avg(sal) from emp);
1、group by 和 having子句
group by用于对查询的结果分组统计 having子句用于限制分组显示结果
问题：如何显示每个部门的平均工资和最高工资？
select avg(sal), max(sal), deptno from emp group by deptno;
注意：这里暗藏了一点，在select列表中所有未包含在组函数的列都应该包含在group by子句中，而包含在group by子句中的列不必包含在select列表中）
eg：显示平均工资低于2000的部门号和它的平均工资？
select avg(sal), max(sal), deptno from emp group by deptno having avg(sal) < 2000;
2、非法使用组函数
1.所有包含于select 列表中，而未包含于组函数中的列都必须包含于 group by 子句中
2.不能在 where子句中使用组函数。可以在 having子句中使用组函数。
对数据分组的总结
1.分组函数只能出现在选择列表、having、order by子句中(不能出现在where中)
2.如果在select语句中同时包含有group by, having, order by 那么它们的顺序是group by, having, order by。
3.在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在group by 子句中，否则就会出错。
如select deptno, avg(sal), max(sal) from emp group by deptno having avg(sal) < 2000;
这里deptno就一定要出现在group by 中
二、子查询
1、子查询 (内查询) 在主查询之前一次执行完成。
2、子查询的结果被主查询(外查询)使用 。
3、注意事项：
子查询要包含在括号内。
将子查询放在比较条件的右侧。
单行操作符对应单行子查询，多行操作符对应多行子查询。
eg：显示与SMITH同部门的所有员工？
思路：第1步，先查询出SMITH的部门号
select deptno from emp where ename = 'SMITH';
第2步，显示
select * from emp where deptno = (select deptno from emp where ename= 'SMITH');
数据库在执行sql 是从左到右扫描的， 如果有括号(子查询)的话，括号(子查询)里面的先被优先执行。
eg：如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
第1步，先查询出部门10的工作
select distinct job from emp where deptno = 10;
第2步，把上面的查询结果看做是一个子查询
select * from emp
where job in (select distinct job from emp where deptno = 10);
（注意：假如括号里面（子查询）返回多行数据，即多个值的时候，不能用job=…，会出错, 说ORA-01427：单行子查询返回多个行。因为等号=是一对一的，所以应该用job in…，也可以使用job =any…(比较少用)）
1、在多行子查询中使用all操作符
问题：如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号？
select ename, sal, deptno from emp where sal > all (select sal from emp where deptno = 30);
方法二：
select ename, sal, deptno from emp where sal > (select max(sal) from emp where deptno = 30);
1
2、在多行子查询中使用any操作符
问题：如何显示工资比部门30的任意一个员工的工资高的员工姓名、工资和部门号？(注意题目说的任意一个，相对应的就是大于最低的那个)
select ename, sal, deptno from emp where sal > any(select sal from emp where deptno = 30);
方法二：
select ename, sal, deptno from emp where sal > (select min(sal) from emp where deptno = 30);
1
3、多列子查询
单行子查询是指子查询只返回单列、单行数据，多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询是指查询返回多个列数据的子查询语句。
eg：如何查询与SMITH的部门和岗位完全相同的所有雇员。
第1步，查出SMITH的部门和岗位
select deptno, job from emp where ename = 'SMITH';
第2步，把上面的查询结果看做是一个子查询
select * from emp
where (deptno, job) = (select deptno, job from emp where ename = 'SMITH');
4、在from子句中使用子查询
请思考：如何显示高于自己部门平均工资的员工的信息
思路：
第1步，查出各个部门的平均工资和部门号
select deptno, avg(sal) mysal from emp group by deptno;
第2步，把上面的查询结果看做是一张子表
select e.ename, e.deptno, e.sal, s.mysal from emp e, (select deptno, avg(sal) mysal from emp group by deptno) s where e.deptno = s.deptno and e.sal > s.mysal;
总结：
（1）>all 相当于大于所有，即要大于最大值；>any 相当于大于任意一个，即要大于最小值。
（2）在这里需要说明的当在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫做内嵌视图，当在from子句中使用子查询时，必须给子查询指定别名。
注意：表别名不能用as，如：
select e.ename, e.deptno, e.sal,s.mysal
from emp e, (select deptno, avg(sal) mysal from emp group by deptno)as s
where e.deptno = s.deptno and e.sal > s.mysal;
在s前不能加as，否则会报错 ，如图
说 ORA-00933：SQL命令未正确结束。（给表取别名的时候，不能加as；但是给列取别名，是可以加as的）。
