leetcode数据库题目及答案汇总
1、 第二高的薪水
![](https://img2018.cnblogs.com/blog/1337989/201902/1337989-20190221141407645-400858200.jpg)
select ifnull((select distinct Salary from Employee order by Salary desc limit 1,1),null) as SecondHighestSalary;
2、第N高的薪水
![](https://img2018.cnblogs.com/blog/1337989/201902/1337989-20190221141452923-1385383046.jpg)
select distinct Salary from Employee e where N = (select count(distinct Salary) from Employee where Salary >= e.Salary );
3、分数排名
![](https://img2018.cnblogs.com/blog/1337989/201902/1337989-20190221141621152-80855910.jpg)
select Score,(select count(distinct score) from Scores where score >= s.score) as Rank from Scores as s order by Score desc;
4、超过经理收入的员工
![](https://img2018.cnblogs.com/blog/1337989/201902/1337989-20190221141709975-397353418.jpg)
select e1.Name as Employee from Employee as e1,Employee as e2 where e1.ManagerId=e2.Id and e1.Salary>e2.Salary;
5、 查找重复的电子邮箱
![](https://img2018.cnblogs.com/blog/1337989/201902/1337989-20190221141807208-988925270.jpg)
select Email from Person group by Email having count(Email)>1;
6、从不订购的客户
![](https://img2018.cnblogs.com/blog/1337989/201902/1337989-20190221141854169-2094101082.jpg)
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、部门工资最高的员工
![](https://img2018.cnblogs.com/blog/1337989/201902/1337989-20190221141937297-1217618328.jpg)
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、删除重复的电子邮箱
![](https://img2018.cnblogs.com/blog/1337989/201902/1337989-20190221142026837-376163316.jpg)
delete p1.* from Person p1,Person p2 where p1.Email=p2.Email and p1.id>p2.id;
9、上升的温度
![](https://img2018.cnblogs.com/blog/1337989/201902/1337989-20190221142113786-931749350.jpg)
select w1.Id from Weather w1 join Weather w2 on DATEDIFF(w1.RecordDate, w2.RecordDate) = 1 where w1.Temperature>w2.Temperature;
10、交换性别
![](https://img2018.cnblogs.com/blog/1337989/201902/1337989-20190221142157416-863956282.jpg)
update salary set
sex = case sex
when 'm' then 'f'
else 'm'
end;