文章简介
本文主要收集了LeetCode上关于MySQL的一些经典考题。
后续也会陆续把所有经典考题补充完整。
175.组合两个表
175.组合两个表
解答:
select p.FirstName as firstName, p.LastName as lastName,a.City as city, a.State as state
from Person p
left join
Address a
on p.PersonId=a.PersonId
说明:比较经典的一道左连接题目。首先,题目有两个表,其次提示是以首个表为中心,第二个表中属性不存在可以为null,从而确定是left join。
181.超过经理收入的员工
181.超过经理收入的员工
解答:
子查询方式(MySQL中的子查询指的是在主查询语句内部使用嵌套的SELECT语句)。找员工的薪资 > 员工的经理的薪资即可。
select name as Employee
from Employee e
where e.salary > (select salary from Employee t where t.id = e.managerId);
JOIN方式
SELECT a.NAME AS Employee
FROM Employee AS a
JOIN Employee AS b
ON a.ManagerId = b.Id
AND a.Salary > b.Salary;
说明:对于只给单张表又有多个主题比较的情况,一般可以通过子查询或者内连接的方式来解决。
182. 查找重复的电子邮箱
182.查找重复的电子邮箱
解答:
select Email
from Person
group by Email
having count(Email) > 1;
说明:当出现去重的需求时,可以首先考虑分组查询 或 UNION联合查询。
196. 删除重复的电子邮箱
196. 删除重复的电子邮箱
解答:
方法一:嵌套
delete from Person where id not in
(
select id from
(
select min(id) as id, count(*) as num from Person group by Email
) t
);
方法二:DELETE + WHERE【官方,不易理解】
DELETE p1 FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id;
197. 上升的温度
197. 上升的温度
解答:
利用日期函数找到了昨天的日期 datediff(a.recordDate,b.recordDate) = 1
select a.id from Weather as a,Weather as b where datediff(a.recordDate,b.recordDate) = 1 and a.Temperature >b.Temperature;
577. 员工奖金
577. 员工奖金
解答:
select name, bonus
from Employee left join Bonus
on Employee.EmpId = Bonus.EmpId
where bonus is null or bonus < 1000;
584. 寻找用户推荐人
584. 寻找用户推荐人
非常简单。解答:
select name from customer where referee_id != 2 or referee_id is null
586. 订单最多的客户
586. 订单最多的客户
解法1:
select customer_number from (
select customer_number, count(*) as total from Orders group by customer_number
) t
order by t.total desc
limit 1
解法2:【标准】
这里很明显需要分组,而且很特别的是,分组的和我们需要的是同一个字段
又因为需要的是最多的数据,所以这里使用LIMIT 1进行分表即可,SQL如下
SELECT
customer_number
FROM
orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC
LIMIT 1;
595. 大的国家
595. 大的国家
非常简单:
SELECT
name, population, area
FROM
world
WHERE
area >= 3000000 OR population >= 25000000;
596. 超过5名学生的课
596. 超过5名学生的课
方法一:
分组并count,然后数量大于等于5。很简单,有个临时表
select class from (
select class, count(*) as total from courses group by class
) t
where t.total >= 5
方法二:【标准解答】
SELECT
class
FROM
courses
GROUP BY class
HAVING COUNT(student) >= 5;
说明:有多个重复的数据且需要统计数量,一般使用GROUP BY