求有点难度的SQL写法

[kevin9078]

EMPNO PEMPNO DEPNAME SALARY
00006 00003 FA 2200
00010 00003 FA 2000
00004 00003 FA 1500
00005 00001 ZX 6000
00002 00001 ZX 4000
00007 00001 ZX 3000
..... ..... ... ....

取每个部门(DEPNAME) , 薪水(SALRRY)最高的前两名

[lmby]

select top 2 * from (select EMPNO, max(PEMPNO), DEPNAME, SALARY from tab group by dep order by salary)

[ab5669]

select EMPNO,PEMPNO,DEPNAME,rank
from (
select EMPNO,PEMPNO,DEPNAME,rank() over SALARY(partition by DEPNAME order by salary desc) as rank )
where rank<=2

[yown]

也可以用动态语句构造:

1.分别求出每个部门的前二位薪水高的,然后再union
select top 2 * from tbl where EMPN='xxx1'order by salary desc
union
select top 2 * from tbl where EMPN='xxx2'order by salary desc
union
select top 2 * from tbl where EMPN='xxx3' order by salary desc

....

[kevin9078]

谢谢各位的支持，问题还是没有解决 , select EMPNO,PEMPNO,DEPNAME,rank
from (
select EMPNO,PEMPNO,DEPNAME,rank() over SALARY(partition by DEPNAME order by salary desc) as rank )
where rank<=2 这段没有看到表的名称要写在哪里

[kevin9078]

谢谢各位 : select EMPNO,PEMPNO,DEPNAME,salary,rank
from (
select EMPNO,PEMPNO,DEPNAME,salary,rank()
over (partition by DEPNAME order by salary desc) as rank from employee)
where rank<=2 测试OK