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<next_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经典实例》