【Leetcode】数据库题目:部门工资最高的员工、部门工资前三高的所有员工

今天看看这两道leetcode数据库题目

 

 

第一道:

Employee 表包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id。

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+


Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+


编写一个 SQL 查询,找出每个部门工资最高的员工。例如,根据上述给定的表格,Max 在 IT 部门有最高工资,Henry 在 Sales 部门有最高工资。

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

 

解答

① 用排名函数先对每个人的薪资分部门来排个名先:

SELECT 
    Name,
    Salary,
    DepartmentId,
    dense_rank() over (partition by DepartmentId order by Salary desc)
FROM
    Employee

② 得到排名信息后,我们还差一个部门名字,所以要拿上面的表连接Department表

SELECT
    b.Name Department,
    a.Name Employee,
    a.Salary Salary
FROM
    (
        SELECT 
            Name,
            Salary,
            DepartmentId,
            dense_rank() over (partition by DepartmentId order by Salary desc) as rank
        FROM
            Employee
    ) a JOIN Department b on a.DepartmentId = b.Id
WHERE
    a.rank = 1  # 把各部门的第一个都给找出来

过了!看看排名,不过得在MS SQL Server才能用窗口函数。leetcode的Mysql还不支持,得8.0版本以上才可以,

 

利用 变量和IF语句 自己实现dense_rank()

SELECT
    b.Name Department,
    a.Name Employee,
    a.Salary Salary
FROM
    (
        SELECT 
            Name,
            Salary,
            DepartmentId,
            if(@pre_DepartmentId = DepartmentId,
              if(@pre_Salary = Salary,@cur_rank, @cur_rank := @cur_rank + 1),
              @cur_rank:=1) as rank
            @pre_Salary := Salary,
            @pre_DepartmentId := DepartmentId
        FROM
            Employee,(SELECT @cur_rank := 0,@pre_DepartmentId := null,@pre_Salary := null)
        ORDER BY
            DepartmentId,
            Salary
    ) a JOIN Department b on a.DepartmentId = b.Id
WHERE
    a.rank = 1

利用 半连接

SELECT 
    d.Name as Department,
    e1.Name as Employee,
    e1.Salary as Salary
FROM
    Employee e1 JOIN Department d on e1.DepartmentId = d.Id
WHERE
    e1.Salary = 
    (
        SELECT
            MAX(e2.Salary)
        FROM
            Employee e2
        WHERE
            e2.DepartmentId = e1.DepartmentId
    )

 

 

第二道:

那其实知道了第一道,第二道的思路就很自然了,变成in(1,2,3 )就行

SELECT 
    b.Name Department,
    a.Name Employee,
    a.Salary Salary
From (
        SELECT 
            Name,
            Salary,
            DepartmentId, 
            DENSE_RANK() over(PARTITION BY DepartmentId ORDER BY Salary Desc) AS rank
        From 
            Employee
) As a join Department b on a.DepartmentId =b.Id
Where 
    a.rank in(1,2,3)

 

 

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值