MySQL篇（leetcode刷题100（排序和分组、函数））（一）（持续更新迭代）
目录
一、排序和分组
1. 每位教师所教授的科目种类的数量
1.1. 题目描述
1.2. 解题思路
2. 查询近30天活跃用户数
2.1. 题目描述
2.2. 解题思路
3. 销售分析III
3.1. 题目描述
3.2. 解题思路
4. 超过5名学生的课
4.1. 题目描述
4.2. 解题思路
5. 求关注者的数量
5.1. 题目描述
5.2. 解题思路
6. 只出现一次的最大数字
6.1. 题目描述
6.2. 解题思路
7. 买下所有产品的客户
7.1. 题目描述
7.2. 解题思路
8. 银行账户概要 II
8.1. 题目描述
8.2. 解题思路
9. 查找重复的电子邮箱
9.1. 题目描述
9.2. 解题思路
方法一：使用 GROUP BY 和临时表
方法二：使用 GROUP BY 和 HAVING 条件
10. 合作过至少三次的演员和导演
10.1. 题目描述
10.2. 解题思路
方法一
方法二
11. 消费者下单频率
11.1. 题目描述
11.2. 解题思路
12. 每天的领导和合伙人
12.1. 题目描述
12.2. 解题思路
13. 上月播放的儿童适宜电影
13.1. 题目描述
13.2. 解题思路
14. 可以放心投资的国家
14.1. 题目描述
14.2. 解题思路
二、聚合函数
1. 有趣的电影
1.1. 题目描述
1.2. 解题思路
2. 平均售价
2.1. 题目描述
2.2. 解题思路
3. 项目员工 I
3.1. 题目描述
3.2. 解题思路
4. 各赛事的用户注册率
4.1. 题目描述
4.2. 解题思路
5. 查询结果的质量和占比
5.1. 题目描述
5.2. 解题思路
6. 每月交易 I
6.1. 题目描述
6.2. 解题思路
7. 即时食物配送 II
7.1. 题目描述
7.2. 解题思路
8. 游戏玩法分析 IV
8.1. 题目描述
8.2. 解题思路
9. 2020年最后一次登录
9.1. 题目描述
9.2. 解题思路
10. 游戏玩法分析 I
10.1. 题目描述
10.2. 解题思路
11. 仓库经理
11.1. 题目描述
11.2. 解题思路
12. 订单最多的客户
12.1. 题目描述
12.2. 解题思路
分组排序取第一
dense_rank()
13. 查找每个员工花费的总时间
13.1. 题目描述
13.2. 解题思路
14. 即时食物配送 I
14.1. 题目描述
14.2. 解题思路
15. 苹果和桔子
15.1. 题目描述
15.2. 解题思路
方法一
方法二
16. 两人之间的通话次数
16.1. 题目描述
16.2. 解题思路
方法一：巧妙使用IF
方法二：自联结
三、高级字符串函数/正则表达式/子句
1. 修复表中的名字
1.1. 题目描述
1.2. 解题思路
2. 患某种疾病的患者
2.1. 题目描述
2.2. 解题思路
3. 删除重复的电子邮箱
3.1. 题目描述
3.2. 解题思路
4. 第二高的薪水
4.1. 题目描述
4.2. 解题思路
5. 按日期分组销售产品
5.1. 题目描述
5.2. 解题思路
6. 列出指定时间段内所有的下单产品
6.1. 题目描述
6.2. 解题思路
7. 查找拥有有效邮箱的用户
7.1. 题目描述
7.2. 解题思路
四、高级主题：窗口函数和公共表表达式（CTE）
1. 项目员工 III
1.1. 题目描述
1.2. 解题思路
2. 找到连续区间的开始和结束数字
2.1. 题目描述
2.2. 解题思路
方法一：窗口函数
方法二：变量
方法三：关联+筛选
3. 每位顾客最经常订购的商品
3.1. 题目描述
3.2. 解题思路
4. 访问日期之间最大的空档期
4.1. 题目描述
4.2. 解题思路
5. 向公司CEO汇报工作的所有人
5.1. 题目描述
5.2. 解题思路
6. 查找成绩处于中游的学生
6.1. 题目描述
6.2. 解题思路
7. 寻找没有被执行的任务对
7.1. 题目描述
7.2. 解题思路
8. 报告系统状态的连续日期
8.1. 题目描述
8.2. 解题思路
方法一
方法二
一、排序和分组
1. 每位教师所教授的科目种类的数量
1.1. 题目描述
1.2. 解题思路
select teacher_id,count(distinct(subject_id)) as cnt
from Teacher
group by teacher_id;2. 查询近30天活跃用户数
2.1. 题目描述
2.2. 解题思路
select
activity_date as day,count(distinct user_id) as active_users
from
Activity
group by
activity_date
having
activity_date > subdate('2019-07-27',30) and activity_date <='2019-07-27'3. 销售分析III
3.1. 题目描述
3.2. 解题思路
select s.product_id,p.product_name
from Sales s
left join Product p
on s.product_id=p.product_id
group by product_id
having min(sale_date)>='2019-01-01' and max(sale_date)<='2019-03-31'4. 超过5名学生的课
4.1. 题目描述
4.2. 解题思路
SELECT class
FROM courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5;5. 求关注者的数量
5.1. 题目描述
5.2. 解题思路
SELECT
user_id,
COUNT(*) AS followers_count
FROM
Followers
GROUP BY user_id
ORDER BY user_id;6. 只出现一次的最大数字
6.1. 题目描述
6.2. 解题思路
select max(num) as num from
(
SELECT
num
FROM
MyNumbers
GROUP BY num
HAVING COUNT(num) = 1
) as t;7. 买下所有产品的客户
7.1. 题目描述
7.2. 解题思路
select customer_id
from Customer
group by customer_id
having count(distinct product_key) in
(select count(distinct product_key) from Product);8. 银行账户概要 II
8.1. 题目描述
8.2. 解题思路
select
u.name,
sum(t.amount) as balance
from
Users as u
left join Transactions as t
on u.account=t.account
group by u.name
having balance>10000;9. 查找重复的电子邮箱
9.1. 题目描述
9.2. 解题思路
方法一：使用 GROUP BY 和临时表
select Email from
(
select Email, count(Email) as num
from Person
group by Email
) as statistic
where num > 1;方法二：使用 GROUP BY 和 HAVING 条件
select Email
from Person
group by Email
having count(Email) > 1;10. 合作过至少三次的演员和导演
10.1. 题目描述
10.2. 解题思路
方法一
select
actor_id,
director_id
from (
select
actor_id,
director_id,
count(timestamp) as ct
from actordirector
group by actor_id, director_id
having ct >= 3
) t;方法二
select
actor_id,
director_id
from actordirector
group by actor_id, director_id
having count(timestamp) >= 3;11. 消费者下单频率
11.1. 题目描述
11.2. 解题思路
select o.customer_id,c.name
from customers c
inner join orders o using(customer_id)
left join product p on o.product_id = p.product_id
group by c.customer_id
having sum(if(order_date like '2020-06%', price*quantity,0))>=100 and
sum(if(order_date like'2020-07%', price*quantity,0))>=10012. 每天的领导和合伙人
12.1. 题目描述
12.2. 解题思路
select
date_id, make_name,
count(distinct(lead_id)) as unique_leads,
count(distinct(partner_id)) as unique_partners
from DailySales
group by date_id, make_name
order by date_id;13. 上月播放的儿童适宜电影
13.1. 题目描述
13.2. 解题思路
SELECT DISTINCT title
FROM TVProgram AS P
LEFT JOIN
Content AS C USING (content_id)
WHERE P.program_date BETWEEN '2020-06-01' AND '2020-06-30'
AND C.Kids_content = 'Y'
AND C.content_type = 'Movies';14. 可以放心投资的国家
14.1. 题目描述
14.2. 解题思路
with country_avg_duration as
(
select
left(a.phone_number, 3) country_code,
avg(b.duration) avg_duration
from Person a
inner join Calls b
on a.id = b.caller_id
or a.id = b.callee_id
group by left(a.phone_number, 3)
)
select
b.name as country
from country_avg_duration a
inner join Country b
on a.country_code = b.country_code
where a.avg_duration > (select avg(duration) from Calls)二、聚合函数
1. 有趣的电影
1.1. 题目描述
1.2. 解题思路
select id, movie, description, rating
from cinema
where description not like "boring" and Mod(id, 2) = 1
order by rating desc;2. 平均售价
2.1. 题目描述
2.2. 解题思路
select distinct a.product_id,round(sum(a.units*b.price)/sum(a.units),2) as average_price
from UnitsSold as a left join prices as b on a.product_id=b.product_id and a.purchase_date between b.start_date and b.end_date
group by a.product_id3. 项目员工 I
3.1. 题目描述
3.2. 解题思路
SELECT project_id, ROUND(AVG(experience_years),2) AS average_years
FROM Project
LEFT JOIN
Employee E on Project.employee_id = E.employee_id
GROUP BY project_id;4. 各赛事的用户注册率
4.1. 题目描述
4.2. 解题思路
select
contest_id,
round(count(user_id)/(select count(user_id) from Users)*100,2) as percentage
from Register
group by contest_id
order by percentage desc,contest_id;5. 查询结果的质量和占比
5.1. 题目描述
5.2. 解题思路
select query_name,
round(avg(rating/position),2) as quality,
round(avg(rating<3)*100,2) as poor_query_percentage
from queries
group by query_name 6. 每月交易 I
6.1. 题目描述
6.2. 解题思路
SELECT DATE_FORMAT(a.trans_date, "%Y-%m") AS month, a.country,
COUNT(a.id) AS trans_count,
COUNT(b.id) AS approved_count,
SUM(a.amount) AS trans_total_amount,
SUM(COALESCE(b.amount,0)) AS approved_total_amount
FROM Transactions AS a
LEFT JOIN (
SELECT id, country, state, amount,
DATE_FORMAT(trans_date, "%Y-%m") AS month FROM Transactions
WHERE state = 'approved'
) AS b
ON a.id = b.id
GROUP BY DATE_FORMAT(a.trans_date, "%Y-%m"), a.country;7. 即时食物配送 II
7.1. 题目描述
7.2. 解题思路
select round(sum(if(order_date=customer_pref_delivery_date,1,0))/count(*)*100, 2) immediate_percentage
from delivery
where (customer_id, order_date) in (
select customer_id, min(order_date) #这里需要注意，仔细分析
from delivery
group by customer_id
)8. 游戏玩法分析 IV
8.1. 题目描述
8.2. 解题思路
SELECT ROUND(avg(a.event_date IS NOT NULL), 2) fraction
FROM
(SELECT player_id, min(event_date) AS login
FROM activity
GROUP BY player_id) p
LEFT JOIN activity a
ON p.player_id = a.player_id AND datediff(a.event_date, p.login) = 19. 2020年最后一次登录
9.1. 题目描述
9.2. 解题思路
# Write your MySQL query statement below
select user_id, max(time_stamp) last_stamp
from Logins
where year(time_stamp)=2020
group by user_id10. 游戏玩法分析 I
10.1. 题目描述
10.2. 解题思路
# Write your MySQL query statement below
select player_id,min(event_date) first_login
from Activity
group by player_id11. 仓库经理
11.1. 题目描述
11.2. 解题思路
# Write your MySQL query statement below
select
distinct w.name as `WAREHOUSE_NAME`,
sum(t.v*w.units) as `VOLUME`
from
Warehouse as `w`
join
(
select
product_id,
Width*Length*Height `v`
from
Products
) as `t`
on w.product_id = t.product_id
group by w.name12. 订单最多的客户
12.1. 题目描述
12.2. 解题思路
分组排序取第一
select
customer_number
from orders group by customer_number
order by count(customer_number) desc limit 1;dense_rank()
select customer_number from (
select
customer_number,
dense_rank() over(order by count(*) desc) as rn
from orders group by customer_number
) temp where rn = 1;13. 查找每个员工花费的总时间
13.1. 题目描述
13.2. 解题思路
select event_day as day, emp_id as emp_id, sum(out_time - in_time) as total_time
from Employees
group by event_day, emp_id14. 即时食物配送 I
14.1. 题目描述
14.2. 解题思路
select round(
(select count(*) from Delivery Where order_date = customer_pref_delivery_date )/
(select count(*) from Delivery),4)*100
as immediate_percentage 15. 苹果和桔子
15.1. 题目描述
15.2. 解题思路
方法一
select sale_date,sum( if(fruit='apples',sold_num,-sold_num)) diff
from Sales
group by sale_date`方法二
select A.sale_date,a_sold_num-o_sold_num diff from (
select sale_date,fruit apples,sold_num a_sold_num from Sales where fruit LIKE 'apples') A inner join (
select sale_date,fruit oranges,sold_num o_sold_num from Sales where fruit LIKE 'oranges') O on A.sale_date = O.sale_date order by sale_date16. 两人之间的通话次数
16.1. 题目描述
16.2. 解题思路
方法一：巧妙使用IF
SELECT
IF(from_id<to_id,from_id,to_id) person1,
IF(from_id>to_id,from_id,to_id) person2,
COUNT(to_id ) as call_count,
SUM(duration) as total_duration
FROM
Calls
GROUP BY person1,person2方法二：自联结
select
from_id person1,
to_id person2,
count(1) call_count,
sum(duration) total_duration
from
(
select * from Calls where from_id < to_id
union all
select to_id from_id,from_id to_id,duration from Calls where from_id > to_id
) t
group by
1,2
order by
1,2三、高级字符串函数/正则表达式/子句
1. 修复表中的名字
1.1. 题目描述
1.2. 解题思路
select user_id , concat(UPPER(SUBSTRING(name,1,1)) , LOWER(SUBSTRING(name,2,LENGTH(name)-1))) name
from Users
order by user_id2. 患某种疾病的患者
2.1. 题目描述
2.2. 解题思路
select patient_id, patient_name, conditions
from Patients
where conditions like '% DIAB1%'
or conditions like 'DIAB1%';3. 删除重复的电子邮箱
3.1. 题目描述
3.2. 解题思路
DELETE
pson1
FROM
Person pson1,Person pson2
WHERE
pson1.Email = pson2.Email AND pson1.Id > pson2.Id4. 第二高的薪水
4.1. 题目描述
4.2. 解题思路
select max(Salary) as SecondHighestSalary from Employee
where Employee.Salary not in (select max(Salary) from Employee )5. 按日期分组销售产品
5.1. 题目描述
5.2. 解题思路
select sell_date,
count(distinct product) num_sold,
group_concat(distinct product order by product) products
from Activities
group by sell_date
order by sell_date6. 列出指定时间段内所有的下单产品
6.1. 题目描述
6.2. 解题思路
SELECT P.product_name, sum(O.unit) as unit FROM Products P LEFT JOIN Orders O ON P.product_id = O.product_id AND left(O.order_date,7) = '2020-02' GROUP BY P.product_name HAVING sum(O.unit) >= 1007. 查找拥有有效邮箱的用户
7.1. 题目描述
7.2. 解题思路
select
*
from
Users
where
mail regexp '^[a-zA-Z][a-zA-Z0-9\_\.\-]*@leetcode\\.com$';四、高级主题：窗口函数和公共表表达式（CTE）
1. 项目员工 III
1.1. 题目描述
1.2. 解题思路
SELECT P.project_id, P.employee_id
FROM Project AS P
LEFT JOIN
Employee AS E on P.employee_id = E.employee_id
WHERE (project_id, experience_years) IN (SELECT project_id, MAX(experience_years) AS MAX
FROM Project
LEFT JOIN
Employee E on Project.employee_id = E.employee_id
GROUP BY project_id);2. 找到连续区间的开始和结束数字
2.1. 题目描述
2.2. 解题思路
方法一：窗口函数
select
min(log_id) start_id,
max(log_id) end_id
from (
select
log_id,
log_id - row_number() over() diff
from logs
) temp group by diff方法二：变量
select
min(log_id) start_id,
max(log_id) end_id
from (
select
log_id,
case when @id = log_id - 1 then @num:=@num else @num:=@num+1 end num,
@id:=log_id
from logs, (select @id:=null, @num:=0) init
) temp group by num;方法三：关联+筛选
select a.log_id as START_ID ,min(b.log_id) as END_ID from
(select log_id from logs where log_id-1 not in (select * from logs)) a,
(select log_id from logs where log_id+1 not in (select * from logs)) b
where b.log_id>=a.log_id
group by a.log_id;3. 每位顾客最经常订购的商品
3.1. 题目描述
3.2. 解题思路
select
b.customer_id,
b.product_id,
c.product_name
from (
select
customer_id,
product_id,
rank() over(partition by customer_id order by cn desc) rk
from (
select
customer_id,product_id,count(1) cn
from Orders
group by customer_id,product_id
)a
)b
left join Products c
on b.product_id = c.product_id
where rk = 1;4. 访问日期之间最大的空档期
4.1. 题目描述
4.2. 解题思路
select
user_id,max(days) biggest_window
from
(
select
user_id, datediff(lead(visit_date,1,'2021-01-01') over(partition by user_id order by visit_date),visit_date ) days
from
UserVisits
) s1
group by
user_id
order by user_id5. 向公司CEO汇报工作的所有人
5.1. 题目描述
5.2. 解题思路
select distinct e3.employee_id
from employees e1,employees e2,employees e3
where e1.employee_id=e2.manager_id
and e2.employee_id=e3.manager_id
and e3.employee_id !=1 and e1.manager_id=16. 查找成绩处于中游的学生
6.1. 题目描述
6.2. 解题思路
select distinct(Exam.student_id) as STUDENT_ID,
Student.student_name as STUDENT_NAME
from Exam
join Student on Student.student_id = Exam.student_id
where Exam.student_id not in
(select distinct(student_id) from Exam
where (exam_id,score) in(select exam_id,max(score) as score from exam group by exam_id) # 实验最高分
or
(exam_id,score) in(select exam_id,min(score) as score from exam group by exam_id) # 实验最低分
)
order by Exam.student_id asc;7. 寻找没有被执行的任务对
7.1. 题目描述
7.2. 解题思路
select a.task_id,convert(a.subtask_id,unsigned integer)subtask_id
from(
select a.task_id,b.subtask_id
from Tasks a,(
select @id:=@id+1 subtask_id from (select @id:=0) as init,Executed
)b
where a.subtasks_count>=b.subtask_id
order by a.task_id,b.subtask_id
)a left join Executed b on a.task_id=b.task_id and a.subtask_id=b.subtask_id
where b.task_id is null 8. 报告系统状态的连续日期
8.1. 题目描述
8.2. 解题思路
方法一
select period_state,
min(date_d) start_date,
max(date_d) end_date
from
(select period_state,date_d,
subdate(date_d,row_number() over(partition by period_state order by date_d)) rn_diff
from
(select 'failed' period_state,fail_date date_d
from failed where fail_date between '2019-01-01' and '2019-12-31'
union all
select 'succeeded' period_state,success_date date_d
from succeeded where success_date between '2019-01-01' and '2019-12-31'
) t
) tt
group by period_state,rn_diff
order by start_date方法二
select state period_state, min(date) start_date, max(date) end_date
from (
select *,
row_number() over (partition by state order by date asc) rk1,
row_number() over (order by date asc) rk2
from (
select fail_date 'date', 'failed' state from failed
union all
select success_date, 'succeeded' from succeeded
) t
) t2
where date between '2019-01-01' and '2019-12-31'
group by state, rk2-rk1