oracle高级查询题目,Oracle经典实例——高级查询

1.结果集分页

窗口函数ROW_NUMBER将会为每一行记录分配一个唯一的数字编号(从1开始递增)：

select row_number() over(order by field1) rn, field1 from table_name;

有了行编号，通过指定的RN值就可以返回任意区间的值

窗口函数ROW_NUMBER用于删除重复记录：

delete from table_name

where t.rowid in (

select t1.rowid from (select t2.rowid,

t2.*,row_number() over(partition by t2.field1, t2.field2 order by t2.field3) rn

from table_name t2) t1

where t2.rn > 1);

2.跳过n行记录

使用窗口函数ROW_NUMBER和MOD跳过编号为偶数的行：

select field1 from(select row_number() over(order by field1) rn,field1) x

where mod(rn,2)=1;

3.在外连接查询里使用OR逻辑

查询部门编号为10和20的员工名字和部门信息，以及部门编号为30和40的部门信息(但不包含员工信息)，

考虑将OR条件放到JOIN子句里：

select e.ename,d.deptno,d.dname,d.loc from dept d

left join emp e on (d.deptno=e.deptno

and (e.deptno=10 or e.deptno=20))

order by 2;

也可以使用内嵌视图过滤EMP.DEPTNO，然后再执行外连接：

select e.ename,d.deptno,d.name,d.loc from dept d

left join (select ename,deptno from emp

where deptno in (10,20)) e

on e.deptno=d.deptno

order by 2;

4.提取最靠前的n行记录

使用窗口函数DENSE_RANK对每个Tie进行一次计数：

select field1,field2 from (

select field1,field2 dense_rank() over (order by field2 desc) dr

from table_name) x where dr<=5;

在排序计算的过程中，如果一个名次上出现了多个候选项，则每一个候选项称为“一个Tie”，

以上查询可能返回函数可能超过5，但只有5种不同的值

5.找出最大和最小的记录

使用窗口函数MIN OVER和MAX OVER分别找出最大和最小小工资的记录：

select ename

from (select ename, sal, min(sal) over() min_sal, max(sal) over() max_sal

from emp) x

where sal in (min_sal, max_sal);

6.查询未来的行

使用窗口函数LEAD OVER查询下一个员工的工资，且工资为递增：

select ename,sal,hirdate from (

select ename,sal,hirdate,

lead(sal)over(order by hirdate) next_sal

from emp

) where sal

7.对结果进行排序

使用窗口函数DENSE_RANK OVER、ROW_NUMBER OVER、RANK OVER，排序变得极其简单方便：

select dense_rank over(order by field1) rnk,field1,field2 from table_name;

8.删除重复项

传统去重的方法是使用DISTINCT或者GROUP BY，另外一种替代方法是使用窗口函数ROW_NUMBER OVER：

select field from (

select field,row_number()over(partition by field order by field) rn

from table_name

) x where rn=1;

整理自《SQL经典实例》