Oracle 高级查询
分组查询
分组函数的概念
分组函数作用于一组数据，并对一组数据返回一个值。
分组函数的使用
分组函数会自动忽略空值，可以通过nvl() 去做特殊处理。
AVG and SUM
// 求出员工的平均工资和工资的总额
select avg(sal), sum(sal) from emp;
MIN and MAX
// 求出员工工资的最大值和最小值
select MAX(sal),MIN(sal) from emp;
COUNT
// 求出员工的总人数
select count(*) from emp;
WM_CONCAT(行转列)
select deptno,wm_concat(ename) from emp group by deptno;
使用Group by 子句数据分组
// 求出各个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
partition by 分区函数
select deptno,row_number() over(partition by ename order deptno) from emp group by deptno;
使用Having 字句过滤分组结果集
不能在Where 子句中使用组函数，可以在Having子句中使用组函数
**where 和 having 可以通用的情况下，尽量使用where **，having是先分组后再过滤，where是先过滤再分组。
GROUP BY语句的增强(做报表)
效果
实现
select deptno,job,sum(sal) from emp group by deptno,job;
+
select deptno,sum(sal) from emp group by deptno;
+
select sum(sal) from emp;
上面三条语句等于下面一条
====
select deptno,job,sum(sal) from emp group by rollup(deptno,job);
多表连接
笛卡尔集(多表查询)
等值连接
查询员工信息，要求显示：员工号，姓名，月薪，部门名称
select e.empno,e.ename,e.sal,d.dname from emp e ,dept d where e.deptno = d.deptno;
不等值连接
查询员工信息，要求显示：员工号，姓名，月薪，员工薪水的级别
select e.empno,e.ename,e.sal,s.grade from emp,salgrade s where e.sal between s.losal and s.hisal;
外连接
按部门统计员工人数，要求显示：部门号，部门名称，人数
// 右外连接(dept 为主表) ，能看懂就可以select d.deptno,d.dname,count(e.empno) 人数from emp e, dept dwhere e.deptno(+)=d.deptnogroup by d.deptno,d,dname;// 还是建议大家这样写select d.deptno,d.dname,count(e.empno) 人数from emp e left join dept don e.deptno=d.deptnogroup by d.deptno,d,dname;// 左外连接（emp 为主表）select d.deptno,d.dname,count(e.empno) 人数from emp e, dept dwhere e.deptno=d.deptno(+)group by d.deptno,d,dname;select d.deptno,d.dname,count(e.empno) 人数from emp e right join dept don e.deptno=d.deptnogroup by d.deptno,d,dname;
自连接
查询员工姓名和员工的老板姓名
select e.ename 员工姓名，b.ename 老板姓名from emp e,emp bwhere e.mgr = b.empno
-
不适合大表操作
select level,empno,ename,sal,mgrfrom empconnect by piror empno = mgrstart with mgr is nullorder by 1;
- 单表查询，没有自连接的结果明显
子查询
可以使用子查询的位置 where,select ,from,having
select 位置使用子查询
在having 位置使用子查询
在from 位置使用子查询
不能使用子查询的位置 group by
Top-N 查询的问题
//查询工资最高的前三位员工信息select rownum, empno,enmame,sal from （select * from emp order by sal desc） where rownum <= 3
行号需要注意的两个问题
- 行号永远按照默认的顺序生成
- 行号只能使用<.，<=；不能使用 >,>=
子查询中null 值的问题（多行的）
案例
分页查询
//查询第5到8行的记录
select r,empno,ename,sal
(select rownum r,empno,ename,sal
from (select rownum ,empno,ename,sal from emp order by sal desc) e1
where rownum <=8) e2
where r >=5
找到员工表中薪水大于本部门平均薪水的员工
select empno,ename,sal,(select avg(sal) from emp where deptno = e.deptno) avgsal
from emp e
where sal > (select avg(sal) from emp where deptno = e.deptno)
select e.empno,e.ename,e.sal,d.avgsal
from emp e, (select deptno,avg(sal) avgsal from emp group by deptno) d
where e.deptno = d.deptno and e.sal > d.avgsal;
有不对的地方，欢迎大家一起讨论。 最后，欢迎大家关注我的微信号“涛涛之海”，您的点赞，收藏，转发就是对我的最大鼓励。
