leetcode上的数据库题汇总(2)

181. Employees Earning More Than Their Managers

https://leetcode.com/problems/employees-earning-more-than-their-managers/

# Write your MySQL query statement below
select e1.Name as Employee
from  Employee  e1, Employee e2 
where e1.ManagerId=e2.Id and e1.Salary>e2.Salary

182. Duplicate Emails

https://leetcode.com/problems/duplicate-emails/

# Write your MySQL query statement below
select distinct p1.Email
from  Person p1, Person p2 
where p1.Email=p2.Email and p1.id<p2.id

183. Customers Who Never Order

https://leetcode.com/problems/customers-who-never-order/

SELECT Name AS Customers 
FROM Customers WHERE Id NOT IN
(SELECT CustomerId FROM Orders);

SELECT Name AS Customers FROM Customers AS c
LEFT JOIN Orders AS o ON c.Id = o.CustomerId
WHERE o.Id IS NULL;

184. Department Highest Salary

https://leetcode.com/problems/department-highest-salary/

select d.Name as Department, e.Name as Employee, e.Salary
from Department d,Employee e, (select MAX(Salary) as Salary,  DepartmentId as DepartmentId from Employee GROUP BY DepartmentId) h
where e.Salary = h.Salary and e.DepartmentId = h.DepartmentId and e.DepartmentId = d.Id;

185. Department Top Three Salaries

https://leetcode.com/problems/department-top-three-salaries/

select d.Name as Department, e.Name as Employee, Salary
from Employee e inner join Department d on e.DepartmentId = d.Id
where 3>(select count(distinct e1.Salary) from Employee e1 where e1.Salary>e.Salary and e1.DepartmentId = e.DepartmentId)