倒在了一道小查询问题面前。

Question

刚才去面试，面试的老师问了下面的这个问题，我没有写出来，估计面试就OVER了，这到无所谓，但是我十分想弄清这个问题，有一张表 结构是这样的
（ID，姓名，部门号，工资）要求，查询出每个部门工资最高的3个员工的记录，并显示出来（如果有N个部门，那么就要显示3*N条记录）这个语句怎么写啊？提示一个思路就行。。

Answer 1

SELECT *
  FROM (SELECT ENAME,
               DEPTNO,
               SAL,
               RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL) RK
          FROM EMP)
 WHERE RK <= 10;-- 2nd way: 
SELECT *
  FROM EMP E1
 WHERE (
        SELECT COUNT(1)
          FROM EMP E2
         WHERE E1.DEPTNO = E2.DEPTNO 
           AND E1.SAL < E2.SAL
       ) < 10;

Answer 2

修改上个帖子 ： 求 前3 就把10 改成3 就可以了 用的到的表是 oracle 9i 中的 emp 表  SQL> desc emp;
 名称                           
 --------------------------
 EMPNO                          
 ENAME                          
 JOB                            
 MGR                            
 HIREDATE                       
 SAL                            
 COMM                           
 DEPTNO         
  EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
------- ---------- --------- ---------- ---------- ---------- ---------- ----------
   7369 SMITH      CLERK           7902 17-12月-80        800                    20
   7499 ALLEN      SALESMAN        7698 20-2月 -81       1600        300         30
   7521 WARD       SALESMAN        7698 22-2月 -81       1250        500         30
   7566 JONES      MANAGER         7839 02-4月 -81       2975                    20
   7654 MARTIN     SALESMAN        7698 28-9月 -81       1250       1400         30
   7698 BLAKE      MANAGER         7839 01-5月 -81       2850                    30
   7782 CLARK      MANAGER         7839 09-6月 -81       2450                    10
   7788 SCOTT      ANALYST         7566 19-4月 -87       3000                    20
   7839 KING       PRESIDENT            17-11月-81       5000                    10
   7844 TURNER     SALESMAN        7698 08-9月 -81       1500          0         30
   7876 ADAMS      CLERK           7788 23-5月 -87       1100                    20  EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
------- ---------- --------- ---------- ---------- ---------- ---------- ----------
   7900 JAMES      CLERK           7698 03-12月-81        950                    30
   7902 FORD       ANALYST         7566 03-12月-81       3000                    20
   7934 MILLER     CLERK           7782 23-1月 -82       1300                    10
                

Answer 3

分析函数select *
  from 
   (select t.*,row_number() over(partition by 部门号 order by 工资) rn
     from table t)
  where rn<=3

Answer 4

FYI: 问题10
http://topic.csdn.net/u/20081002/00/f8d90ba2-e2bb-412a-a0c5-1b6d518fc22a.html

Answer 5

SELECT *
  FROM (SELECT EID,
               ENAME,
               DEPTNO,
               SAL,
               ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RN
          FROM EMP)
 WHERE RN <= 3;

Answer 6

面试不会因为一个问题没有打上来就把你cancel的，是全面的评价，要有信心。正确方法：
SELECT ID,姓名,部门号,工资 
  FROM (SELECT ID,姓名,部门号,工资, 
              row_number() OVER(PARTITION BY 部门号 ORDER BY 工资) rn 
          FROM 员工表) 
WHERE Rn <= 3;

Answer 7

分组函数可以高顶了。参照一下
SQL> select * from emp;EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               1014 rows selectedSQL> 
SQL> select deptno, sal
  2    from (select deptno,
  3                 sal,
  4                 row_number() over(partition by deptno order by sal desc) rn
  5            from emp)
  6   where rn <= 3;DEPTNO       SAL
------ ---------
    10   5000.00
    10   2450.00
    10   1300.00
    20   3000.00
    20   3000.00
    20   2975.00
    30   2850.00
    30   1600.00
    30   1500.00

Answer 8

谢谢阿 我看了以后茅塞顿开
row_number() OVER(PARTITION BY 部门号 ORDER BY 工资) 这个是什么意思啊？我不太理解。。

Answer 9

就是分组函数，俺部门号分组，按工资排序，比如就成下面的样子。
SQL> select emp.*,
  2                 row_number() over(partition by deptno order by sal desc) rn
  3            from emp
  4  ;EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO         RN
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
 7839 KING       PRESIDENT       1981-11-17    5000.00               10          1
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10          2
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10          3
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20          1
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20          2
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20          3
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20          4
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20          5
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30          1
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30          2
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30          3
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30          4
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30          5
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30          614 rows selected

Answer 10

都是高手。select * from (select t.* , row_number() over(partition by t.col1 order by t.col2) rn from tablename t) where rn<=number;

Answer 11

2楼的兄弟 你也挺厉害的
面试主要是真对java的，就问了3个数据库的问题，那两个很简单，就是连接查询的问题了。。
就这个，我就没回答上来。。