leetcode数据库题目及答案汇总

1、第二高的薪水

select ifnull((select distinct Salary from Employee order by Salary desc limit 1,1),null) as SecondHighestSalary;

2、第N高的薪水

select distinct Salary from Employee e where N = (select count(distinct Salary) from Employee where Salary >= e.Salary );

3、分数排名

select Score,(select count(distinct score) from Scores where score >= s.score) as Rank from Scores as s order by Score desc;

4、超过经理收入的员工

select e1.Name as Employee from Employee as e1,Employee as e2 where e1.ManagerId=e2.Id and e1.Salary>e2.Salary;

5、查找重复的电子邮箱

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

6、从不订购的客户

select c.Name as Customers from Customers as c left join Orders as o on c.Id = o.CustomerId where c.Id not in (select c.Id from Customers as c,Orders as o where c.Id = o.CustomerId);

7、部门工资最高的员工

select d.Name as Department,e.Name as Employee,e.Salary from Department as d inner join Employee as e on d.Id = e.DepartmentId and e.Salary>=(select max(Salary) from Employee where DepartmentId=d.Id);

8、删除重复的电子邮箱