求一条sql,在oracle的scott用户下查询各部门工资排在前三名员工的部门编号，部门名称，姓名，工资

[prettygirlchq]

求一条sql,在oracle的scott用户下查询各部门工资排在前三名员工的部门编号，部门名称，姓名，工资！谢了.....

[xpingping]

select 部门名称,姓名,工资
from (select 部门名称,姓名,工资,row_number() over(partition by 部门名称 order by 工资)rn
from tb) 
where rn <=3

[prettygirlchq]

好像不对吧？

[yu15tian]

2楼就是。。。。！

[小海葵1]

WITH table1 AS
(
     SELECT '001' AS bmbh, 'AAA' AS  bmnc,'張三A' AS xm,'1200' AS gz FROM dual
     union all
     SELECT '001' AS bmbh, 'AAA' AS  bmnc,'李四A' AS xm,'1200' AS gz FROM dual
     union all
     SELECT '001' AS bmbh, 'AAA' AS  bmnc,'王五A' AS xm,'1300' AS gz FROM dual
     union all
     SELECT '001' AS bmbh, 'AAA' AS  bmnc,'趙六A' AS xm,'1100' AS gz FROM dual
     union all
    　SELECT '002' AS bmbh, 'BBB' AS  bmnc,'張三B' AS xm,'2500' AS gz FROM dual
     union all
     SELECT '002' AS bmbh, 'BBB' AS  bmnc,'李四B' AS xm,'2300' AS gz FROM dual
     union all
     SELECT '002' AS bmbh, 'BBB' AS  bmnc,'王五B' AS xm,'2400' AS gz FROM dual
     union all
     SELECT '002' AS bmbh, 'BBB' AS  bmnc,'趙六B' AS xm,'2600' AS gz FROM dual     
)
SELECT bmbh,bmnc,xm,gz FROM (
select RANK()OVER(PARTITION BY bmbh ORDER BY GZ DESC) AS R,DENSE_RANK()OVER(PARTITION BY bmbh ORDER BY GZ DESC) AS DR,TABLE1.* from table1)
WHERE R <=3 --并列排名时后面不连续
     -- DR <=3 --并列排名时后面连续