Oracle中的复杂查询、分页
一、分组查询
【注意】
- 分组查询查询的结果是整个分组的共同特性;
- 分组查询中，出现在group by后面的【原始列】才能出现在select后面，没有出现在gropu by 后面的原始列，如果想在select后面，必须加上 聚合函数 (聚合函数可以将多列值变成一个值)
在一个非子查询单元中所有条件都不能使用 select 后面的别名来判断，不管是where还是having【从底层执行顺序可以推到出来】
1). 单表查询的完整语法
SELECT *|列名 FROM 表名 {WEHRE 查询条件} {GROUP BY 分组字段} ORDER BY 列名1 ASC|DESC，列名 2...ASC|DESC
2). 案例演示
- 查询出每个部门的平均工资
select
e.deptno 部门编号,
avg(e.sal) 平均薪资
from
emp e
group by
e.deptno
;
- 查询出平均工资高于2000的部门信息
select
*
from
dept d
where
d.deptno in
(select
e.deptno
from
emp e
group by
e. deptno
having
avg(e.sal) > 2000);
- 查询出每个部门工资高于800的员工的平均工资
- where 是过滤分组前的顺序； where 在group by之前
- having 是过滤分组之后的顺序； having在group by之后
*/
select
e.deptno 部门编号,
avg(e.sal) 高于800的平均工资
from
emp e
where
e.sal > 800
group by
e.deptno;
- 查询出每个部门的员工工资工资高于800，且平均工资高于2000的部门信息
select
e.deptno 部门编号,
avg(e.sal) 平均工资
from
emp e
where
e.sal > 800
group by
e.deptno
having
avg(e.sal) > 2000;
二、多表查询
注意：笛卡尔积的概念以及sql完整语法就不在这里阐述了
1). 等值连接【推荐】
select * from emp,dept where emp.deptno = dept.deptno;
2). 内连接
select * from emp e inner join dept d on e.deptno = d.deptno;
3). 左/右外连接
select * from emp e left join dept d on e.deptno = d.deptno;
select * from emp e right join dept d on e.deptno = d.deptno;
- Oracle中的外连接方言【不推荐使用】
Oracle中的专用外连接 (+)；尽量选择通用的写法
select
*
from
emp e,
dept d
where
e.deptno(+) = d.deptno;
4). 自连接
自连接：在一张表上，站在不同的角度看成不同的表
- 查询出员工姓名，员工领导姓名
select
e1.ename 员工名,
e2.ename 领导姓名
from
emp e1
left join
emp e2
on
e1.mgr = e2.empno
- 查询出员工姓名，员工部门名称，员工领导姓名，员工领导部门名称
select
e1.ename 员工姓名,
d1.dname 员工所在部门,
e2.ename 领导,
d2.dname 领导部门
from
emp e1, emp e2, dept d1, dept d2
where
e1.mgr = e2.empno
and e1.deptno = d1.deptno
and e2.deptno = d2.deptno;
- 查询出每个员工编号，姓名，部门名称，工资等级和他的上级领导的姓名，工资等级
select
e.empno,
e.ename,
decode(s.grade,
1,'一级',
2,'二级',
3,'三级',
4,'四级',
5,'五级'
) grade,
d.dname,
e1.empno,
e1.ename,
decode(s1.grade,
1,'一级',
2,'二级',
3,'三级',
4,'四级',
5,'五级'
) grade
from
emp e, emp e1, dept d, salgrade s, salgrade s1
where
e.mgr = e1.empno
and e.deptno = d.deptno
and e.sal between s.losal and s.hisal
and e1.sal between s1.losal and s1.hisal
三、子查询
1). 查询的结果为一个值
---- 查询出工资和SCOTT一样的员工信息
select * from emp where sal in
(select sal from emp where ename = 'SCOTT');
2). 查询的结果为一个集合
---- 查询出工资和10号部门任意员工一样的员工信息
select * from emp where sal in
(select sal from emp where deptno = 10);
3). 查询的结果为一张表(多条记录)
在返回多条记录的子查询可以把它的结果集当做一张表，给起个别名
---- 查询出每个部门最低工资，和最低工资员工姓名，和该员工所在部门名称
select
b.empno 员工id,
b.ename 员工姓名,
a.deptno 部门id,
c.dname 部门名称,
a.min_sal 部门最低工资
from
(select deptno, min(sal) min_sal
from emp
group by deptno) a,
emp b,
dept c
where
a.deptno = b.deptno
and
a.min_sal = b.sal
and
a.deptno = c.deptno;
四、rownum与分页查询
rownun:表示行号，实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现。当我们做select操作的时候，每查询出一行记录，就会在该行上加一个行号，行号从1开始，依次递增，不能跳着走
排序操作会打乱rownum的顺序，应为查询出来之后，rownum已经生成，再次排序将会打乱顺序！为了解决这个问题，可以先排序再加上行号，解决方案就是【嵌套查询】
可以理解为rownum直接不支持大于号，只支持小于号
1). 使用rownum + group by排序的解决方案
如果涉及到排序，但是还是要使用rownum的话，可以使用嵌套查询
select rownum, t.* from
(select rownum luan, e.* from emp e order by e.sal desc) t;
2). 案例演示
select
rownum,
e.*
from
(select * from emp order by sal desc) e
where
rownum < 11 and rownum > 5;
rownum行号不写上大于一个正数，类似于rownum > 10 是不正确的！因为rownum上面的案例中，由于where比最外面的select先执行，所以逻辑上当rownum等于1是判断 1>5不成立，rownum不能跳着走，中断查询！所以查询结果为空
3). 【分页查询的固定格式】
分页查询的解决方案就是：【起别名 + 三(多)层嵌套】;起别名之后，再被外部调用，就变成一个普通的列名！
select * from
(select rownum rn,e.* from
(select * from emp order by sal desc) e
where rownum < 11)
where rn > 5;
- 第一层查询原始数据
- 第二层嵌套解决order by引起的顺序打乱
- 第三层解决rownum不能跳着走，因为从1开始生成
