leetcode SQL题（184和185）

leetcode SQL题（184和185）

184

编写SQL查询以查找每个部门中薪资最高的员工。
按 任意顺序 返回结果表。
查询结果格式如下例所示。

select d.name Department ,e.name Employee , e.salary Salary 
from  Employee e join Department d
on e.departmentId=d.id
where (e.departmentId,e.salary) in
(select departmentId,max(salary) from Employee group by departmentId);

185

公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。

编写一个SQL查询，找出每个部门中 收入高的员工 。

以 任意顺序 返回结果表。

查询结果格式如下所示。

select d.name Department ,e.name Employee , e.salary Salary 
from  Employee e join Department d
on e.departmentId=d.id
where (e.departmentId,e.salary) in
(select a.departmentId,a.salary from 
(select departmentId,salary,(dense_rank() over(partition by departmentId order by salary desc)) "rank" from Employee) a
where a.rank<=3);

题解

两道题思路类似

因为 Employee 表包含 Salary 和 DepartmentId 字段，我们可以以此在部门内查询最高工资。

-- 184
SELECT
    DepartmentId, max(salary)
FROM
    Employee
GROUP BY DepartmentId;

-- 185
select a.departmentId,a.salary from 
(select departmentId,salary,(dense_rank() over(partition by departmentId order by salary desc)) "rank" from Employee) a
where a.rank<=3

注意：有可能有多个员工同时拥有最高工资，所以最好在这个查询中不包含雇员名字的信息。

然后，我们可以把表 Employee 和 Department 连接，再在这张临时表里用 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
   (上面选出的那个表)

over(partition by)同group by功能简介

over 函数 配合聚合函数(max、min、sum、avg、count等)或row_number等函数，可以在不改变原显示数据的情况下，新增一列作为聚合函数的计算值；
group by 子句只能同聚合函数(max、min、sum、avg、count),对相关列进行分组，只能展示出分组列和聚合列的数据。
over(partition by)比group by 具有更多的用武之地，具有更高级的功能