查询每个部门工资前三的员工

疯狂的码农呀

于 2020-03-30 15:51:22 发布

阅读量2.9k

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL基础文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/qq_37176833/article/details/105201168

SQL基础专栏收录该内容

4 篇文章

订阅专栏

本文介绍如何使用SQL查询每个部门中薪资排名前三位的员工，以及如何实现查询每个班级成绩前三名的学生。通过精心设计的SQL语句，可以有效地进行分组和排名操作，满足业务需求。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.查询每个部门工资前三的员工

员工表

在这里插入图片描述

部门表

在这里插入图片描述
代码：

#select d.Name as Department,e.Name as Employee,e.Salary as Salary from Employee e,Department d where e.DepartmentId = d.Id
select bb.Name as Department,aa.Name as Employee,aa.Salary as Salary from
(
select * from Employee e
where exists(select count(*) from Employee em where em.Salary >= e.Salary 
AND em.DepartmentId = e.DepartmentId  group BY em.DepartmentId HAVING COUNT(DISTINCT em.Salary)<=3) 
) 
as aa,Department as bb 
where aa.DepartmentId = bb.Id
order by Department,Salary desc

代码参实例2：

#mysql实现分组查询每个班级的前三名
select a.class,a.score from student a where (select count(*) from student where a.class=class and a.score<score)<3
order by a.class, a.score desc;