*LeetCode Mysql查找排名前n的数值

最新推荐文章于 2024-07-27 02:04:34 发布

凉心良意

最新推荐文章于 2024-07-27 02:04:34 发布

阅读量232

点赞数

分类专栏： SQL 文章标签： leetcode mysql sql

本文链接：https://blog.csdn.net/weixin_44355591/article/details/106078418

版权

SQL 专栏收录该内容

21 篇文章 3 订阅

订阅专栏

编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工

Employee:

Id	Name	Salary	DepartmentId
1	Joe	85000	1
2	Henry	80000	2
3	Sam	60000	2
4	Max	90000	1
5	Janet	69000	1
6	Randy	85000	1
7	Will	70000	1

Department:

Id	Name
1	IT
2	Sales

All in the Code:

SELECT 
    d.Name AS "Department", e1.Name AS "Employee", e1.Salary
FROM
    Employee e1 JOIN Department d 
    on e1.DepartmentId = d.Id
WHERE
    3 > (SELECT COUNT(DISTINCT e2.Salary)
        FROM 
            Employee e2
        WHERE
            e2.Salary > e1.Salary
            AND e1.DepartmentId = e2.DepartmentId);