LeetCode Database(181-185)

181. Employees Earning More Than Their Managers

select E.Name Employee
from Employee E, Employee M
where E.ManagerId = M.Id and E.Salary > M.salary;

select E.name Employee
from Employee E
inner join Employee M
on E.ManagerId = M.Id and E.salary > M.Salary;

182. Duplicate Emails

分组查询

select Email
from Person
group by Email
having count(*) > 1;

自连接

select distinct a.Email
from Person a join Person b
on a.Email = b.Email
where a.id != b.id;

子查询

select distinct a.Email
from Person a
where exists(
    select 1
    from Person b
    where a.Email = b.Email
    limit 1,1
);

183. Customers Who Never Order

使用not in:

select Name as Customers
from Customers c 
where c.Id not in (select CustomerId from Orders o);

使用not esists:

select Name as Customers
from Customers c
where not exists(select CustomerId from Orders o where o.CustomerId = c.id);

使用left join:

select Name as Customers
from Customers c
left join Orders o
on c.Id = o.CustomerId where o.Id is NULL;

184. Department Highest Salary

首先使用临时表t查询出每一个部门的最高薪水，然后使用薪水值和部门Id与雇员表Employee做内连接，再通过部门Id与部门表Department做内连接。

select d.Name as Department, e.Name as Employee, t.Salary from
Employee e
inner join(
    select DepartmentId, max(Salary) as Salary
    from Employee 
    group by DepartmentId) t
using(DepartmentId, Salary)
inner join
Department d
on d.id = t.DepartmentId;

185. Department Top Three Salaries

select d.Name as Department, t.Name as Employee, Salary 
from(
    select  DepartmentId,
            Name,
            Salary,
            @rank := IF(@prevDeptId != DepartmentId, 1,
                        IF(@prevSalary = Salary, @rank, @rank+1)) as Rank,
            @prevDeptId := DepartmentId as prevDeptId,
            @prevSalary := Salary as prevSalary
    from    Employee e, (select @rank := 0, @prevDeptId := NULL, @prevSalary := NULL) r
    order by DepartmentId asc, Salary desc
)t inner join Department d on t.DepartmentId = d.Id
where t.rank <= 3;

select D.Name as Department, E.Name as Employee, E.Salary as Salary
from Employee E, Department D
where (select count(distinct(Salary)) from Employee
        where DepartmentId = E.DepartmentId and Salary > E.Salary) < 3
and E.DepartmentId = D.Id
order by E.DepartmentId, E.Salary desc;