用一条SQL语句查询分组前三名数据

表简单表示为：

EmpNo 员工编号
EName 员工姓名
Sal   员工工资
DepNo 部门编号

 

表名：T   用一条SQL语句在员工表中查询出来每个部门公司前三名的数据

 

 

select a.DepNo,a.Sal,b.Sal,c.Sal from (select distinct DepNo,Sal from T a where Sal in (select top 1 Sal from T where DepNo=a.DepNo order by Sal desc) ) a

,(select DepNo,Sal from T a where id in (select top 2 id from T where DepNo=a.DepNo order by Sal desc) and id not in (select top 1 id from T where DepNo=a.DepNo order by Sal desc) ) b

,(select DepNo,Sal from T a where id in (select top 3 id from T where DepNo=a.DepNo order by Sal desc) and id not in (select top 2 id from T where DepNo=a.DepNo order by Sal desc) ) c

where a.DepNo=b.DepNo and a.DepNo=c.DepNo order by a.DepNo