倒在了一道小查询问题面前。
刚才去面试，面试的老师问了下面的这个问题，我没有写出来，估计面试就OVER了，这到无所谓，但是我十分想弄清这个问题，
有一张表 结构是这样的
（ID，姓名，部门号，工资）
要求，查询出每个部门工资最高的3个员工的记录，并显示出来（如果有N个部门，那么就要显示3*N条记录）
这个语句怎么写啊？提示一个思路就行。。
[解决办法]
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;
[解决办法]
修改上个帖子 ： 求 前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
[解决办法]
分析函数
- SQL code
select * from (select t.*,row_number() over(partition by 部门号 order by 工资) rn from table t) where rn<=3
[解决办法]
FYI: 问题10
http://topic.csdn.net/u/20081002/00/f8d90ba2-e2bb-412a-a0c5-1b6d518fc22a.html
[解决办法]
- SQL code
SELECT * FROM (SELECT EID, ENAME, DEPTNO, SAL, ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RN FROM EMP) WHERE RN <= 3;
[解决办法]
- SQL code
面试不会因为一个问题没有打上来就把你cancel的，是全面的评价，要有信心。正确方法：SELECT ID,姓名,部门号,工资 FROM (SELECT ID,姓名,部门号,工资, row_number() OVER(PARTITION BY 部门号 ORDER BY 工资) rn FROM 员工表) WHERE Rn <= 3;
[解决办法]
4 楼的高手很厉害 我之前也有这个问题 是4楼的给的思路 顺便问下楼主 还有哪些面试题 顺便学习下
[解决办法]
分组函数可以高顶了。参照一下
- SQL code
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
[解决办法]
就是分组函数，俺部门号分组，按工资排序，比如就成下面的样子。
- SQL code
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
[解决办法]
都是高手。
- SQL code
select * from (select t.* , row_number() over(partition by t.col1 order by t.col2) rn from tablename t) where rn<=number;