查询每个部门(deptname)中的工资(sal)前三名的人员姓名(name)

Question

SQL Server查询每个部门(deptname)中的工资(sal)前三名的人员姓名(name),这几个字段都在同一个表中table1

Answer 1

如果是sql server:

select t.name from (
  select name,  Rank()
    over (PARTITION BY deptname ORDER BY sal desc) as Rank from table
  ) t where Rank <=3;

Answer 2

select name
from table1 t1
where
(

select count(1)     
from table1 t2     
where t2.deptname=t1.deptname and t2.sal>=t1.sal  

) <4
order by deptname, sal desc;