SQL每日刷题—子查询 多表连接 窗口函数

题目

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

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

编写一个 SQL 查询，找出每个部门工资最高的员工。
对于上述表，您的 SQL 查询应返回以下行（行的顺序无关紧要）。

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

预备知识

①in 操作符允许我们在 WHERE 子句中规定多个值（多个值加括号即可）

• IN 与 = ANY 等价，均表示，变量在（子查询）列表之中，即 a IN (table B) 表示 a = ANY B.b

• IN 与 EXISTS 的性能区别主要来自，IN 会编列子查询的每行记录，然后再返回，而EXISTS 则只要遇到第一个满足条件的记录就马上返回。

②窗口函数

转载自：https://mp.weixin.qq.com/s/f34352G-3yk1qB_HjQh5Yg

③多表连接操作

• 交叉连接查询(基本不会使用,得到的是两个表的乘积)

  select * from A,B;

• 内连接查询(使用的关键字 inner join – inner可以省略)

  隐式内连接：select * from A,B where 条件;

  显示内连接：select * from A inner join B on 条件;

• 外连接查询(使用的关键字 outer join – outer可以省略)

  左外连接：left outer join

  select * from A left outer join B on 条件;

  右外连接：right outer join

  select * from A right outer join B on 条件;

④子查询：一条select语句结果作为另一条select语法一部分（查询条件，查询结果，表等）。

select …查询字段 … from … 表… where … 查询条件

思路

### 方法一：子查询

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

SELECT
    DepartmentId, MAX(Salary)
FROM
    Employee
GROUP BY DepartmentId;

2. 我们可以把表 Employee 和 Department 连接，再在这张临时表里用 IN 语句查询部门名字和工资的关系。

### 方法二：窗口函数

在Employee表中按照DepartmentId 分组聚合，按照Salary降序排列

可以拓展到查询任意排列顺序的记录

代码

select 
    d.Name as Department,
    e.Name as Employee,
    e.Salary 
from 
    Employee e,Department d 
where
    e.DepartmentId=d.id 
    and
    (e.Salary,e.DepartmentId) 
    in 
    (select max(Salary),DepartmentId 
    from Employee 
    group by DepartmentId);

SELECT S.NAME as DEPARTMENT, S.EMPLOYEE, S.SALARY
FROM (SELECT D.NAME,T.NAME EMPLOYEE,T.SALARY,
             dense_rank() OVER(PARTITION BY T.DepartmentId  
             ORDER BY T.Salary DESC) as RN
      FROM EMPLOYEE T
      JOIN DEPARTMENT D
      ON T.DEPARTMENTID = D.ID) as S
WHERE S.RN = 1

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/department-highest-salary