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;