大数据第37天—Mysql练习题8-杨大伟

需求一：编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

展示效果：

Department	Employee	Salary
IT	Jim	90000
IT	Max	90000
Sales	Henry	80000

Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int);
Create table If Not Exists Department (Id int, Name varchar(255));

insert into Employee (Id, Name, Salary, DepartmentId) values (1, 'Joe', 75000, 1);
insert into Employee (Id, Name, Salary, DepartmentId) values (2, 'Jim', 90000, 1);
insert into Employee (Id, Name, Salary, DepartmentId) values (3, 'Henry', 80000, 2);
insert into Employee (Id, Name, Salary, DepartmentId) values (4, 'Sam', 60000, 2);
insert into Employee (Id, Name, Salary, DepartmentId) values (5, 'Max', 90000, 1);
insert into Employee (Id, Name, Salary, DepartmentId) values (6, 'Randy', 85000, 1);
insert into Employee (Id, Name, Salary, DepartmentId) values (7, 'Will', 70000, 1);

insert into Department (Id, Name) values (1, 'IT');
insert into Department (Id, Name) values (2, 'Sales');

最终SQL:

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
    );

需求二：编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。

展示效果：

Department	Employee	Salary
IT	Max	90000
IT	Jim	90000
IT	Randy	85000
IT	Joe	75000
Sales	Henry	80000
Sales	Sam	60000

最终SQL:

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