最近有许多学员问了一些面试中的问题，请数据库教师总结了一下：总结起来看：一是关于怎样找出和去除重复数据，这在另一个帖子利已有详细介绍。

二是关于找出某一列里最大或最小的前几个，或是大于或小于某一个值（最大值或平均值）的数据。

针对这种情况，再此做一个介绍。

1：找出公司里收入最高的前三名员工：SQL> select rownum, last_name, salary2 from (select last_name, salary3 from s_emp4 order by salary desc)5 where rownum<=3;ROWNUM LAST_NAME SALARY---------- ------------------------- ----------1 V elasquez 47502 Ropeburn 29453 Nguyen 2897.5注意：请大家分析一下一下语句为什么不对：SQL> select rownum, last_name, salary2 from s_emp3 where rownum<=34 order by salary desc;ROWNUM LAST_NAME SALARY---------- ------------------------- ----------1 V elasquez 47503 Nagayama 26602 Ngao 20002：找出表中的某一行或某几行的数据：（1）：找出表中第三行数据：用以下方法是不行的，因为rownum后面至可以用<或<=号,不可以用=,>号和其它的比较符号。

SQL> select * from s_emp2 where rownum=3;no rows selectedSQL> select * from s_emp2 where rownum between3 and 5;no rows selected正确的方法如下：SQL> l1 select last_name, salary2 from (select rownum a, b.*3 from s_emp b)4* where a=3SQL> /LAST_NAME SALARY ------------------------- ----------Nagayama 2660（2）：找出第三行到第五行之间的数据：SQL> l1 select last_name, salary2 from (select rownum a, b.*3 from s_emp b)4* where a between 3 and 5SQL> /LAST_NAME SALARY------------------------- ----------Nagayama 2660Quick-To-See 2755Ropeburn 29453：找出那些工资高于他们所在部门的平均工资的员工。

（1）：第一种方法：SQL> select last_name, dept_id, salary2 from s_emp a3 where salary>(select avg(salary)4 from s_emp5 where dept_id=a.dept_id);LAST_NAME DEPT_ID SALARY ------------------------- ---------- ----------V elasquez 50 4750 Urguhart 41 2280 Menchu 42 2375Biri 43 2090 Catchpole 44 2470Havel 45 2483.3Nguyen 34 2897.5 Maduro 41 2660Nozaki 42 2280 Schwartz 45 209010 rows selected.（2）：第二种方法：SQL> l1 select st_name, a.salary, a.dept_id, b.avgsal2 from s_emp a, (select dept_id, avg(salary) avgsal3 from s_emp4 group by dept_id) b5 where a.dept_id=b.dept_id6* and a.salary>b.avgsalSQL> /LAST_NAME SALARY DEPT_ID A VGSAL ------------------------- ---------- ---------- ----------V elasquez 4750 50 3847.5Urguhart 2280 41 2181.5Menchu 2375 42 2055.16667Biri 2090 43 1710Catchpole 2470 44 1995Havel 2483.3 45 2069.1Nguyen 2897.5 34 2204Maduro 2660 41 2181.5Nozaki 2280 42 2055.16667Schwartz 2090 45 2069.110 rows selected.4：找出那些工资高于他们所在部门的manager的工资的员工。

SQL> l1 select id, last_name, salary, manager_id2 from s_emp a3 where salary>(select salary4 from s_emp5* where id=a.manager_id)SQL> /ID LAST_NAME SALARY MANAGER_ID ---------- ------------------------- ---------- ----------6 Urguhart 2280 27 Menchu 2375 28 Biri 2090 29 Catchpole 2470 210 Havel 2483.3 212 Giljum 2831 313 Sedeghi 2878.5 314 Nguyen 2897.5 315 Dumas 2755 316 Maduro 2660 610 rows selected.找出部门工资排名第二，三的员工1 select name,salary,deptno from (2 select concat(last_name,first_name) name,salary,department_id deptno,3 rank() over (partition by department_id order by salary desc) rnk4* from employees) where rnk=2 or rnk=3SQL> /NAME-------------------------------------------------------------------------------- SALARY DEPTNO---------- ----------FayPat6000 20KhooAlexander3100 30BaidaShelli2900 30NAME-------------------------------------------------------------------------------- SALARY DEPTNO---------- ----------WeissMatthew8000 50KauflingPayam7900 50ErnstBruce6000 60NAME-------------------------------------------------------------------------------- SALARY DEPTNO---------- ----------AustinDavid4800 60PataballaV alli4800 60PartnersKaren13500 80NAME-------------------------------------------------------------------------------- SALARY DEPTNO---------- ----------ErrazurizAlberto12000 80KochharNeena17000 90De HaanLex17000 90NAME-------------------------------------------------------------------------------- SALARY DEPTNO---------- ----------FavietDaniel9000 100ChenJohn8200 100GietzWilliam8300 11015 rows selected.SQL>找出部门工资排名第二，三的员工1 select name,salary,deptno from (2 select concat(last_name,first_name) name,salary,department_id deptno,3 rank() over (partition by department_id order by salary desc) rnk4* from employees) where rnk=2 or rnk=3SQL> /NAME-------------------------------------------------------------------------------- SALARY DEPTNO---------- ----------FayPat6000 20KhooAlexander3100 30BaidaShelli2900 30NAME-------------------------------------------------------------------------------- SALARY DEPTNO---------- ----------WeissMatthew8000 50KauflingPayam7900 50ErnstBruce6000 60NAME-------------------------------------------------------------------------------- SALARY DEPTNO---------- ----------AustinDavid4800 60PataballaV alli4800 60PartnersKaren13500 80NAME-------------------------------------------------------------------------------- SALARY DEPTNO---------- ----------ErrazurizAlberto12000 80KochharNeena17000 90De HaanLex17000 90NAME-------------------------------------------------------------------------------- SALARY DEPTNO---------- ----------FavietDaniel9000 100ChenJohn8200 100GietzWilliam8300 110 15 rows selected. SQL>又是一道面试题: 原表:id proid proname 1 1 M1 2 F2 1 N2 2 G3 1 B3 2 A查询后的表:id pro1 pro21 M F2 N G3 B A写出查询语句又是一道面试题: 原表:id proid proname 1 1 M1 2 F2 1 N2 2 G3 1 B3 2 A查询后的表:id pro1 pro21 M F2 N G3 B A写出查询语句又是一道面试题: 原表:id proid proname 1 1 M1 2 F2 1 N2 2 G3 1 B3 2 A查询后的表:id pro1 pro21 M F2 N G3 B A写出查询语句又是一道面试题:原表:id proid proname1 1 M1 2 F2 1 N2 2 G3 1 B3 2 A查询后的表:id pro1 pro21 M F2 N G3 B A写出查询语句解决方案可有以下三种作参考：1：使用pl/sql代码实现，但要求你组合后的长度不能超出oracle varchar2长度的限制。