Day2 employees who exceed the manager’s income

Time:2022-5-8

This question uses MySQL 8 0, not on mysql5 It has been tested in version 6 and is not guaranteed to be correct.

subject

Title Source:Employees who exceed the manager’s income

Managers also belong to employees. Each employee has an ID and a list of managers corresponding to the employeeId。 Check the names of employees who earn more than their managers

create table employee (
    id int,
    name varchar(255),
    salary int,
    managerId int
);

insert into employee values 
(1, 'Joe', 70000, 3),
(2, 'Henry', 80000, 4),
(3, 'Sam', 60000, null),
(4, 'Max', 90000, null);

SQL

select employee.name from employee left join employee e
on employee.managerId = e.id
where employee.salary > e.salary;

analysis

managerIdIt’s the manageridAt the same time, the manager is also an employee, that is to say, NomanagerIdIt’s an ordinary employee, yesmanagerIdIt’s the manager.

So willemployeeSelf connection, connection condition isemployee.managerId = e.id, you can connect ordinary employees with managers.

Then filter outemployee.salary > e.salaryJust your employees.

More problem solving References:https://github.com/astak16/bl…