【LeetCode--数据库】工资问题

627. 交换工资（简单）

题目：给定一个salary 表，如下所示，有m=男性 和 f=女性的值 。交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求使用一个更新查询，并且没有中间临时表

| id | name | sex | salary |
|----|------|-----|--------|
| 1  | A    | m   | 2500   |
| 2  | B    | f   | 1500   |
| 3  | C    | m   | 5500   |
| 4  | D    | f   | 500    |

期望结果：

| id | name | sex | salary |
|----|------|-----|--------|
| 1  | A    | f   | 2500   |
| 2  | B    | m   | 1500   |
| 3  | C    | f   | 5500   |
| 4  | D    | m   | 500    |

解法：使用if函数

update salary
set sex = if(sex='m','f','m');

184. 部门工资最高的员工（中等）

题目：Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id；Department 表包含公司所有部门的信息；编写一个 SQL 查询，找出每个部门工资最高的员工。

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

期望结果：

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

解法：连接两个表，where子句按部门id分组查找最高的工资，再通过部门id找到对应部门名字

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

185. 部门工资前三高的员工（困难）

 

题目：Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id ；Department 表包含公司所有部门的信息；编写一个 SQL 查询，找出每个部门工资前三高的员工

 

+----+-------+--------+--------------+         +-----+-------+
| Id | Name  | Salary | DepartmentId |         | Id  | Name  |
+----+-------+--------+--------------+         +-----+-------+
| 1  | Joe   | 70000  | 1            |         |  1  |  IT   |
| 2  | Henry | 80000  | 2            |         |  2  | Sales |
| 3  | Sam   | 60000  | 2            |         +-----+-------+
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
+----+-------+--------+--------------+

期望结果：

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

解法：连接两个表，

where子句的意思是，在该部门中比这三名员工工资高的不超过3人

SELECT d.Name AS Department, e1.Name AS Employee, e1.Salary  
FROM Employee e1 JOIN Department d ON e1.DepartmentId = d.Id  
WHERE  (SELECT COUNT(DISTINCT e2.Salary) FROM Employee e2  
           WHERE e2.Salary > e1.Salary AND e1.DepartmentId = e2.DepartmentId) < 3;