大数据第37天—Mysql练习题8-杨大伟
需求一：编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。
展示效果：
| Department | Employee | Salary |
|---|---|---|
| IT | Jim | 90000 |
| IT | Max | 90000 |
| Sales | Henry | 80000 |
1 Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int); 2 Create table If Not Exists Department (Id int, Name varchar(255)); 3 4 insert into Employee (Id, Name, Salary, DepartmentId) values (1, 'Joe', 75000, 1); 5 insert into Employee (Id, Name, Salary, DepartmentId) values (2, 'Jim', 90000, 1); 6 insert into Employee (Id, Name, Salary, DepartmentId) values (3, 'Henry', 80000, 2); 7 insert into Employee (Id, Name, Salary, DepartmentId) values (4, 'Sam', 60000, 2); 8 insert into Employee (Id, Name, Salary, DepartmentId) values (5, 'Max', 90000, 1); 9 insert into Employee (Id, Name, Salary, DepartmentId) values (6, 'Randy', 85000, 1); 10 insert into Employee (Id, Name, Salary, DepartmentId) values (7, 'Will', 70000, 1); 11 12 insert into Department (Id, Name) values (1, 'IT'); 13 insert into Department (Id, Name) values (2, 'Sales');
最终SQL:
1 SELECT 2 Department.name AS 'Department', 3 Employee.name AS 'Employee', 4 Salary 5 FROM 6 Employee 7 JOIN 8 Department ON Employee.DepartmentId = Department.Id 9 WHERE 10 (Employee.DepartmentId , Salary) IN 11 ( SELECT 12 DepartmentId, MAX(Salary) 13 FROM 14 Employee 15 GROUP BY DepartmentId 16 );
需求二：编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。
| Department | Employee | Salary |
|---|---|---|
| IT | Max | 90000 |
| IT | Jim | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 75000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
最终SQL:
1 SELECT 2 d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary 3 FROM 4 Employee e1 5 JOIN 6 Department d ON e1.DepartmentId = d.Id 7 WHERE 8 3 > (SELECT 9 COUNT(DISTINCT e2.Salary) 10 FROM 11 Employee e2 12 WHERE 13 e2.Salary > e1.Salary 14 AND e1.DepartmentId = e2.DepartmentId 15 );
