当前位置：网站首页>LeetCode: 184. 部门工资最高的员工

LeetCode: 184. 部门工资最高的员工

2022-07-22 09:11:00 【东西方集大成者】

原题：https://leetcode-cn.com/problems/department-highest-salary/

个人解法:

利 用 窗 口 函 数 ： 利用窗口函数： 利用窗口函数：

select Department.Name as Department, e1.Name as Employee, e1.Salary
from (
    select *
    from (
        select *, dense_rank() over(partition by DepartmentId order by Salary Desc) as ranking
        from Employee
    ) as temp
    where temp.ranking = 1
) as e1
left join Department
on e1.DepartmentId = Department.Id
where Department.Id IS NOT NULL;

在这里插入图片描述
注意Department表无任何数据的特殊情况，故需要判断Department.Id IS NOT NULL;

官方解法:

亮 点 : 两 个 字 段 一 起 I N 亮点: 两个字段一起IN 亮点:两个字段一起IN

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
	)
;
原网站

版权声明
本文为[东西方集大成者]所创，转载请带上原文链接，感谢
https://whoami.blog.csdn.net/article/details/114478174

边栏推荐

猜你喜欢

随机推荐