mysql求部门薪资前三的用户信息

方法一：

select * from emp e1
where 
(select count(distinct e2.sal) from emp e2 where e2.sal>e1.sal and e1.deptno=e2.deptno)<3
order by e1.deptno,e1.sal desc

方法二：

select * 
from (select t1.*, (select count(*) from emp t2 where t1.sal<=t2.sal and t1.deptno=t2.deptno) as rownum 
			from emp t1) t3 
where rownum <=3 order by deptno,sal DESC;

方法三：

select * from emp a 
where exists 
(select count(*) from emp where deptno = a.deptno and sal > a.sal having Count(*) < 3)
order by a.deptno,sal DESC;

补充：
求部门薪资最高的人
正确

select * from
(select * from emp order by emp.sal desc) e1
group by deptno

错误

select * from emp group by deptno order by sal desc

---

作者：孙文旭
来源：CSDN
原文：https://blog.csdn.net/qq_35495339/article/details/95107293
版权声明：本文为博主原创文章，转载请附上博文链接！