小虾米,出难题. 小丹尼, 挺招急. 要答案, 看这里.

REM If you have Oracle database installed on your home,
REM You can do following test on SCOTT account, or HR
REM Account.
SQL> connect scott
Enter password:
Connected.
SQL>
SQL> select deptno,ename, sal
2 from (
3 select deptno,ename,sal,rank() over (partition by deptno order by deptno,sal desc) as rank_sal
4 from emp
5 order by deptno,sal desc
6 )
7 where rank_sal <6
8 /

DEPTNO ENAME SAL
---------- ---------- ----------
10 MILLER 8000
10 KING 5000
10 CLARK 2450
20 SMITH 5800
20 SCOTT 3000
20 FORD 3000
20 JONES 2975
20 ADAMS 1100
30 BLAKE 2850
30 ALLEN 1600
30 TURNER 1500

DEPTNO ENAME SAL
---------- ---------- ----------
30 WARD 1250
30 MARTIN 1250

13 rows selected.

SQL>
SQL>
SQL> connect HR
Enter password:
Connected.
SQL>
SQL> select department_id,last_name, salary
2 from (
3 select department_id,last_name,salary,rank() over (partition by department_id order by department_id,salary desc) as rank_sal
4 from employees
5 order by department_id,salary desc
6 )
7 where rank_sal <6
8 /

DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
10 Whalen 4400
20 Hartstein 13000
20 Fay 6000
30 Raphaely 11000
30 Khoo 3100
30 Baida 2900
30 Tobias 2800
30 Himuro 2600
40 Mavris 6500
50 Fripp 8200
50 Weiss 8000

DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
50 Kaufling 7900
50 Vollman 6500
50 Mourgos 5800
60 Hunold 9000
60 Ernst 6000
60 Austin 4800
60 Pataballa 4800
60 Lorentz 4200
70 Baer 10000
80 Russell 14000
80 Partners 13500

DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
80 Errazuriz 12000
80 Ozer 11500
80 Cambrault 11000
80 Abel 11000
90 King 24000
90 Kochhar 17000
90 De Haan 17000
100 Greenberg 12000
100 Faviet 9000
100 Chen 8200
100 Urman 7800

DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
100 Sciarra 7700
110 Higgins 12000
110 Gietz 8300
Grant 7000

37 rows selected.

所有跟帖： 

• 谢老土，我也用了一样的QUERY。要学的东东真多啊！ -小虾米- ♀ (0 bytes) () 07/20/2009 postreply 15:52:22