关于Oracle的几个技巧小面题

1.有一个员工表empinfo结构如下

 create table empinfo(
	    fempno	varchar2(10) not null primary key,
	    fempname varchar2(20) not null, 
        fage number(2) not null, 
        fsalary number(10,2) not null
      );

      insert into empinfo(fempno,fempname,fage,fsalary) values('1','AA',30,7000);
      insert into empinfo(fempno,fempname,fage,fsalary) values('2','BB',31,8000);
      insert into empinfo(fempno,fempname,fage,fsalary) values('3','CC',32,9000);
      insert into empinfo(fempno,fempname,fage,fsalary) values('4','DD',33,10000);
      insert into empinfo(fempno,fempname,fage,fsalary) values('5','EE',34,11000);
      insert into empinfo(fempno,fempname,fage,fsalary) values('6','FF',35,12000);
      insert into empinfo(fempno,fempname,fage,fsalary) values('7','GG',36,13000);
      insert into empinfo(fempno,fempname,fage,fsalary) values('8','FF',37,14000);

假如该表有大约1000万条记录，写一条最高效的SQL语句，计算以下4种人中每种员工的数量
第1种人：fsalary>9999 and fage>35
第2种人：fsalary>9999 and fage<35
第3种人：fsalary<9999 and fage>35
第4种人：fsalary<9999 and fage<35
提示：使用一条SQL搞定

  select sum(case when fsalary>9999 and fage>35 then 1 else 0 end) "第一种人",
         sum(case when fsalary>9999 and fage<35 then 1 else 0 end) "第二种人",
         sum(case when fsalary<9999 and fage>35 then 1 else 0 end) "第三种人",
         sum(case when fsalary<9999 and fage<35 then 1 else 0 end) "第四种人" 
        from empinfo;

哈哈，是不是看到之后就豁然开朗，巧妙运用sum和case，对运算结果进行累加，最后得到我们的结果

2.不准用组函数（Max），取得最高薪水，你能写出几种方法–三种

1. 第一种—利用Rownum，排序后直接截取

 select rownum,s.* from (select ename,sal from emp order by sal desc) s where rownum=1;

2. 第二种—利用distinct—不重复

  select ename,sal from emp where sal not in (select distinct e.sal from emp e join emp t on e.sal < t.sal);

3. 第三种—利用集合查询的差集运算结合Rownum

 select rownum,s.* from (select ename,sal from emp order by sal) s where rownum<=14
 minus
select rownum,s.* from (select ename,sal from emp order by sal) s where rownum<=13;

值得一提的是：在进行集合查询的差集运算时，一定要将范围大的放在前面查询范围小的放在后面查询，之前就放反了，然后啥也没查到，郁闷半天。。。。

3.找到员工表中薪水大于【本】部门平均薪水的员工

  EMPNO ENAME          SAL     AVGSAL
  ----- ---------- ------- ----------
  7499 ALLEN         1600       1566
  7566 JONES         2975       2175
  7698 BLAKE         2850       1566
  7788 SCOTT         3000       2175
  7839 KING          5000       2916
  7902 FORD          3000       2175

select e.empno,e.ename,e.sal,b.avgsal from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) b where e.deptno=b.deptno and e.sal>b.avgsal;

4.找到员工表中工资最高的前三名

  ROWNUM EMPNO ENAME      SAL
  ------ ----- ---------- -------
  1      7839   KING        5000
  2      7788   SCOTT       3000
  3      7902   FORD        3000

    select rownum,ename,sal from
    (select ename,sal from emp order by sal desc) 
    where rownum < 4;