目录
题目一:组合两个表
题目链接:https://leetcode-cn.com/problems/combine-two-tables/
思路关键:题目要求无论person是否有地址信息,都需要基于上述两表提供person的信息
因此,需要采用left join on进行连接
select firstName ,LastName,City,State
from Person
left join Address
on Person.PersonId = Address.PersonId;
题目二:第二高的薪水
题目链接:https://leetcode-cn.com/problems/second-highest-salary/
思路:有两个注意地方:
1)如何找到第二高的薪水? 先使用order by进行排序,然后使用distinct进行过滤重复数据
接着使用limit 限制输出数量,使用offset确定起始偏移量。
2)如果不存在第二高的薪水,查询返回为null.
先用一层select查询出结果,外层再使用一层select进行结果集的查询。
select (
select distinct Salary
from Employee
order by Salary desc
limit 1 offset 1 )
as SecondHighestSalary;
题目三:第n高的薪水
题目链接:https://leetcode-cn.com/problems/nth-highest-salary/
注意:求第n高薪水和求第2高薪水框架类似,主要要注意的一点在于:
limit后面不能含运算表达式,只能为非负整数或者变量
故提前SET N := N-1;
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N := N-1;
RETURN (
# Write your MySQL query statement below.
select distinct salary from Employee
order by salary desc
LIMIT N, 1
);
END
题目四:分数排名
题目链接:https://leetcode-cn.com/problems/rank-scores/
注意:1)这是排序rank用法的使用。记住rank/dense_rank/row_number的用法
2)dense_rank() over( order by 字段 desc)
3)RANK是mysql中保留字,如果要作为列名,需要加引号。
select Score, (DENSE_RANK() over( order by Score desc) ) as 'Rank'
from Scores;
题目五:连续出现的数字
题目链接:https://leetcode-cn.com/problems/consecutive-numbers/
思路:按照题目意思来做就可以,先给表取三个别名,然后从表中取三个数据,三个数据的ID相邻,num相等的
输出结果的时候别忘来使用distinct去重。
题目六:超过经理收入的员工
题目链接:https://leetcode-cn.com/problems/employees-earning-more-than-their-managers/
思路:涉及表的自联结.
注意连接条件:条件是e1.ManagerId = e2.Id ,而不是e1.Id = e2.Id
select e1.Name as 'Employee'
from Employee e1
join Employee e2
on e1.ManagerId = e2.Id
and e1.Salary > e2.Salary;
题目七:查找重复的电子邮箱
题目链接:https://leetcode-cn.com/problems/duplicate-emails/
思路:表的自联结:注意自联结条件:
id不等,但是email相等
select distinct p1.Email
from Person p1
join Person P2
on p1.Email = p2.Email
and p1.Id != p2.Id;
题目八:从不订购的客户
题目链接:https://leetcode-cn.com/problems/customers-who-never-order/
注意:查询有订购记录的客户比较好找,我们此处是找没有订购的客户。
select Name as 'Customers' from Customers
where Id not in
(select CustomerId from Orders);
题目九:部门工资最高的员工
题目链接:https://leetcode-cn.com/problems/department-highest-salary/
思路:逐步求解,我们先用里层select找到各个部门ID及其对应的最高工资。
然后使用外层select,和in操作找到部门名称,员工名称,工资。
select Department.Name as 'Department', e2.Name as Employee, e2.Salary
from Employee e2
join Department
on e2.DepartmentId = Department.Id
where (e2.DepartmentId,e2.Salary) in
(select e1.DepartmentId, max(e1.Salary)
from Employee e1
group by DepartmentId);
题目十:删除重复的邮箱
题目链接:https://leetcode-cn.com/problems/delete-duplicate-emails/
注意:1)此题的要求是进行删除,不是查询;
2)删除我们直接是delete p1,而不是delete p1.Id,p1.Email;
delete p1
from Person p1,
Person p2
where p1.id >p2.Id
and p1.Email = p2.Email;