oracle技术点滴--sql篇1

介绍下个人觉得可能比较少用但用处颇大的sql:
1.over()分析函数
  求按deptno分组统计出sum(sal)的同时，还要看到其它字段信息，如何实现？
  select sum(t.sal) from emp t group by t.deptno;
  是肯定不行的，可改成如下sql:
  select t.*,sum(t.sal) over(partition by t.deptno) sum  from emp t;
  查询结果如下:(部分)

     EMPNO ENAME      JOB
---------- ---------- ---------
      7782 CLARK      MANAGER
      7839 KING       PRESIDENT
      7934 MILLER     CLERK
      7566 JONES      MANAGER
      7902 FORD       ANALYST
      7876 ADAMS      CLERK
      7369 SMITH      CLERK
      7788 SCOTT      ANALYST
      7521 WARD       SALESMAN
      7844 TURNER     SALESMAN
      7499 ALLEN      SALESMAN
      7900 JAMES      CLERK
      7698 BLAKE      MANAGER
      7654 MARTIN     SALESMAN

已选择14行。
   select t.*,sum(t.sal) over(partition by t.deptno) sum  from emp t;语句也支持排序，
   亦可换成
    select t.*,sum(t.sal) over(partition by t.deptno order by t.empno) sum  from scott.emp t;

2.row_number() over()
  求按deptno分组，empno排序列出所有员工顺序。
  select t.*,row_number() over(partition by t.deptno order by empno) sum  from emp t;
  此函数还有其它几种类似使用

3.9i版本增加merge into语法。
  合并两张表数据。emp1与emp表结构完全一致或部分一致，需要将emp中数据更新或插入至emp1中，下面sql原意为：将emp1与emp表在empno字段上关联，如果匹配则依赖emp的数据更新至emp1中，不匹配则将emp数据插入emp1中。
merge into emp1 e1
using emp e
on (e1.empno = e.empno)
when matched then
update set e1.job = e.job,e1.ename = e.ename
when not matched then
insert values(e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno);


从10g版本亦可如下单独部分使用:
merge into emp1 e1
using emp e
on (e1.empno = e.empno)
when matched then
update set e1.job = e.job,e1.ename = e.ename
或
using emp e
on (e1.empno = e.empno)
when not matched then
insert values(e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno);
或

merge into emp1 e1
using emp e
on (e1.empno = e.empno)
when matched then
update set e1.job = e.job,e1.ename = e.ename delete where e.empno = 111when not matched then
insert values(e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno);

4.insert sqll
  顾名思义，多表插入。比较简单，不做太多描述。
  只报示例过程贴出。
  创建两张示例表
  create table emp1 as select * from emp where 1 = 2;
  create table emp2 as select * from emp where 1 = 2;
  执行
insert all
when sal > 1500 then
into emp1(empno,ename,sal) values(empno,ename,sal)
when sal < 2500 then
into emp2(empno,ename,sal) values(empno,ename,sal)
select empno ,ename ,sal from scott.emp; 

5.nulls first或null last
  oracle中null值为最大,
  select *  from emp t order by comm与
  select *  from emp t order by comm nulls first;
  查询结果一样，都将comm列为null排最前。可改成
  select *  from emp t order by comm nulls last
  comm列为null的记录将排至最后