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;

分区函数partition by 的用法

使用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 d
where e.deptno(+)=d.deptno
group by d.deptno,d,dname;
// 还是建议大家这样写
select  d.deptno,d.dname,count(e.empno) 人数
from emp e left join dept d
on e.deptno=d.deptno
group by d.deptno,d,dname;
// 左外连接（emp 为主表）
select  d.deptno,d.dname,count(e.empno) 人数
from emp e, dept d
where e.deptno=d.deptno(+)
group by d.deptno,d,dname;
select  d.deptno,d.dname,count(e.empno) 人数
from emp e right join dept d
on e.deptno=d.deptno
group by d.deptno,d,dname;

自连接

查询员工姓名和员工的老板姓名

select e.ename 员工姓名，b.ename 老板姓名
from emp e,emp b
where e.mgr = b.empno

• 不适合大表操作

select level,empno,ename,sal,mgr
from emp
connect by piror empno = mgr
start with mgr is null
order 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;

有不对的地方，欢迎大家一起讨论。
最后，欢迎大家关注我的微信号，您的点赞，收藏，转发就是对我的最大鼓励。