手撕SQL：employee表新增departmentId字段，联查department表（字段：department、employee、salary），查找出每个部门中薪资最高的员工，按任意顺序返回结果表。

## 解法一（推荐，MySQL 8.0+ 窗口函数） ```sql SELECT d.name AS department, e.name AS employee, -- 若没有 e.name，请改为 e.id AS employee e.salary FROM ( SELECT e.*, DENSE_RANK() OVER (PARTITION BY e.departmentId ORDER BY e.salary DESC) AS rnk FROM Employee e ) e JOIN Department d ON d.id = e.departmentId WHERE e.rnk = 1; ``` **说明** * 按 `departmentId` 分区，对 `salary` 进行降序排名；`rnk=1` 即该部门最高薪。 * 自然支持**并列第一**（会返回多行）。 ## 解法二（MySQL 5.x / 通用 SQL，无窗口函数） **写法 2A：子查询求每部门最大薪资，再回连原表** ```sql SELECT d.name AS department, e.name AS employee, -- 若没有 e.name，请改为 e.id AS employee e.salary FROM Employee e JOIN Department d ON d.id = e.departmentId JOIN ( SELECT departmentId, MAX(salary) AS max_salary FROM Employee GROUP BY departmentId ) m ON m.departmentId = e.departmentId AND m.max_salary = e.salary; ``` **说明** * 子查询 `m` 先求出每个部门的最高工资； * 再和 Employee 按部门+薪资回连，命中即最高薪员工； * **并列第一**会自然返回多行。 **写法 2B：相关子查询（更直观，但性能略弱）** ```sql SELECT d.name AS department, e.name AS employee, -- 若没有 e.name，请改为 e.id AS employee e.salary FROM Employee e JOIN Department d ON d.id = e.departmentId WHERE e.salary = ( SELECT MAX(e2.salary) FROM Employee e2 WHERE e2.departmentId = e.departmentId ); ``` ### 复杂度 & 选型 * **窗口函数方案**：可读性强，性能好（一次扫描+排序）。 * **分组回连方案**：兼容旧版本 MySQL，生产常用。 > 注：如果你的 `Employee` **没有** `name` 列，把上面 SQL 里的 `e.name AS employee` 改成 `e.id AS employee` 即可。