高级查找

1.给结果集分页

 

   select * from (select row_number() over(order by sal) as rn,sal from emp) x
where rn between 6 and 10

 

2.跳过表中n行

  select * from(
     select row_number() over(order by ename) rn,ename from emp
   ) where mod(rn,2) = 1

 

3.在外联中用"OR"逻辑

  ①

 

     select * from dept t left join emp p on (p.deptno = t.deptno
     and (p.deptno='10' or p.deptno='20'))

  ②

     select * from dept t left join (select * from emp where deptno = '10' or

     deptno = '20') p on  t.deptno = p.deptno

 

4.确定哪些行可互换

  select v1.* from v v1,v v2 where v1.test1 = v2.test2 and v1.test2 = v2.test1

 

5.选择前n个记录

 

  select empno,ename from (
select empno,ename,dense_rank() over(order by sal) rn from emp
) where rn < 5

 

6.查找包含最大值和最小值的记录

  select distinct emp.empno,emp.ename,sal from emp,(
select empno,ename,min(sal) over() minsal,max(sal) over() maxsal from emp
) where sal = minsal or sal = maxsal

 

7.存取"未来"行

  select empno,ename,hiredate,sal from(
select empno,ename,hiredate,lead(hiredate) over(order by hiredate) nexthiredate,
       sal,lead(sal) over(order by hiredate) nextsal from emp
) where hiredate <= nexthiredate and sal < nextsal

 

8.轮转行值

  select ename,sal,nvl(lead(sal) over(order by sal),min(sal) over()) forward,
                 nvl(lag(sal) over(order by sal),max(sal) over()) rewind
from emp 

 

9.给结果分等级

10.抑制重复

  select job from(select job,row_number() over(partition by job order by job) rn from emp) x
where rn = 1   

 

11.取骑士值

 select empno,ename,job,hiredate,sal,deptno,
 max(sal) keep(dense_rank last order by hiredate) over(partition by deptno) latest_sal
from emp
order by deptno   

 

12.生成简单的预测 

 with nrows as(
select level n from dual connect by level <= 3
)
select id,order_date,process_date,
       case when nrows.n >= 2 then process_date + 1 else null end verified,
       case when nrows.n = 3 then process_date + 2 else null end shipped
from(
   select nrows.n id,sysdate+ nrows.n as order_date,sysdate + nrows.n + 2 as process_date from nrows
) orders,nrows

 

 

13.long类型数据导入到分区表的解决办法

 

1.create table tt (id int,content long);

 declare
  for i in 1 .. 10000 loop
    insert into tt values(i,'Hello World');
  end loop;
  commit;
end;
 2.   create table t_par(id int,content clob)
    partition by range(id)
    (partition t_part1 values less than (3000),
    partition t_part2 values less than (6000),
    partition t_part3 values less than (9000),
    partition t_part4 values less than (12000))
   
3. alter table tt modify content clob;    
  

insert into t_par(id,content) (select * from tt);