目录
一、排序和分组
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