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
也可以用动态语句构造: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....
谢谢各位的支持,问题还是没有解决 , 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 这段没有看到表的名称要写在哪里
谢谢各位 : 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
from (
select EMPNO,PEMPNO,DEPNAME,rank() over SALARY(partition by DEPNAME order by salary desc) as rank )
where rank<=2
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....
from (
select EMPNO,PEMPNO,DEPNAME,rank() over SALARY(partition by DEPNAME order by salary desc) as rank )
where rank<=2 这段没有看到表的名称要写在哪里
from (
select EMPNO,PEMPNO,DEPNAME,salary,rank()
over (partition by DEPNAME order by salary desc) as rank from employee)
where rank<=2 测试OK