【学习笔记】LeetCode SQL刷题（高频50基础版+进阶版）

一、高频SQL50题(基础版)

题目考查类型题号查询1-5连接6-14聚合函数15-22排序和分组23-29高级查询和连接30-36子查询37-43高级字符串函数 / 正则表达式 / 子句44-50

1. 1757. 可回收且低脂的产品

SELECT product_id
FROM Products 
WHERE low_fats='Y'and recyclable='Y';

1. 584. 寻找用户推荐人

select name
from Customer
where referee_id !=2or referee_id isnull

1. 595. 大的国家

select name,population,area
from World
where area>=3000000or population>=25000000

1. 1148. 文章浏览 I

selectdistinct author_id as id
from Views
where author_id=viewer_id
orderby id

1. 1683. 无效的推文

select tweet_id
from Tweets
where length(content)>15

1. 1378. 使用唯一标识码替换员工ID

select unique_id,name
from Employees leftjoin  EmployeeUNI
on EmployeeUNI.ID=Employees.ID

1. 1068. 产品销售分析 I

select product_name,year,price
from Sales join Product
on Sales.product_id = Product.product_id

1. 1581. 进店却未进行过交易的顾客

select customer_id,count(*)as count_no_trans
from Visits v leftjoinTransactions t on
v.visit_id = t.visit_id
where transaction_id isnullgroupby customer_id

1. 197. 上升的温度

select w2.id id
from Weather w1 join Weather w2
on w1.recordDate = w2.recordDate-interval1daywhere w1.Temperature < w2.Temperature

1. 1661. 每台机器的进程平均运行时间

# 运行时间17%selectstart.machine_id,round(avg(end.timestamp-start.timestamp),3) processing_time
from(select*from Activity
where activity_type ='start')asstartjoin(select*from Activity
where activity_type ='end')asendonstart.machine_id=end.machine_id andstart.process_id=end.process_id
groupbystart.machine_id

# 运行时间45%select machine_id,round((2*sum(timestamp*(casewhen activity_type ='start'then-1else1end)))/count(activity_type),3)as processing_time
from Activity
groupby machine_id

11.577. 员工奖金

select name,bonus
from Employee leftjoin Bonus
on Employee.empId = Bonus.empid
where bonus<1000or bonus isnull

12.1280. 学生们参加各科测试的次数

select a.student_id,a.student_name,a.subject_name,ifnull(attended_exams,0) attended_exams
from(select*from subjects join students) a
leftjoin(select*,count(e.student_id)as attended_exams
from Examinations e
groupby e.student_id,e.subject_name) b

on a.student_id = b.student_id
and a.subject_name = b.subject_name

orderby a.student_id,a.subject_name

13.570. 至少有5名直接下属的经理

select e2.name name
from Employee e2
leftjoin Employee e1
on  e1.managerId=e2.id
groupby e2.id
havingcount(*)>=5

14.1934. 确认率

select  s.user_id,round(sum(if(action='confirmed',1,0))/count(*),2) confirmation_rate
from Signups  s
leftjoin Confirmations c
on s.user_id = c.user_id
groupby s.user_id

15.620. 有趣的电影

select*from cinema
where description!='boring'and id%2!=0orderby rating desc

16.1251. 平均售价

select p.product_id,ifnull(round((sum(price*units)/sum(units)),2),0)as average_price
from Prices  p leftjoin UnitsSold u
on p.product_id = u.product_id
and u.purchase_date between p.start_date and p.end_date
groupby p.product_id

17.1075. 项目员工 I

select project_id,round(avg(experience_years),2)as  average_years
from Project p leftjoin Employee e
on p.employee_id = e.employee_id
groupby project_id

18.1633. 各赛事的用户注册率

select contest_id,round(count(contest_id)/(selectcount(*)from Users)*100,2)as percentage
from Register r leftjoin Users u
on r.user_id = u.user_id
groupby contest_id
orderby percentage desc,contest_id

19.1211. 查询结果的质量和占比

select query_name,round(avg(rating/position),2)as quality,round((100*sum(casewhen rating<3then1else0end)/count(*)),2)as poor_query_percentage
from Queries
groupby query_name
having query_name isnotnull

20.1193. 每月交易 I

selectleft(trans_date,7)asmonth,
country,count(*)as trans_count,sum(casewhen state='approved'then1else0end)as approved_count,sum(amount)as trans_total_amount,sum((casewhen state='approved'then1else0end)*amount)as approved_total_amount
fromTransactionsgroupbymonth,country

21.1174. 即时食物配送 II

selectround((sum(casewhen customer_pref_delivery_date=order_date then1else0end)*100/count(*)),2)as immediate_percentage
from(select customer_id,min(order_date)as order_date,min(customer_pref_delivery_date)as customer_pref_delivery_date
from Delivery
groupby customer_id)as first_order

22.550. 游戏玩法分析 IV

# 卡了很久最小时间selectround(count(*)/(selectcount(distinct player_id)from Activity),2)as fraction
from((select player_id,min(event_date)as event_date
from Activity 
groupby player_id)as a1

join Activity a2
on a1.player_id=a2.player_id
and a1.event_date=a2.event_date -interval1day)

23.2356. 每位教师所教授的科目种类的数量

select teacher_id,count(distinct subject_id)as cnt
from teacher
groupby teacher_id

24.1141. 查询近30天活跃用户数

select activity_date asday,count(distinct user_id)as active_users
from Activity
groupby activity_date
having activity_date between("2019-07-27"-interval29day)and"2019-07-27"

25.1084. 销售分析III

# 注意sum=count的用法，用于“所有都是……”的场景select s.product_id,product_name
from Sales s leftjoin Product p
on s.product_id=p.product_id
groupby  s.product_id
havingsum(s.sale_date between"2019-01-01"and"2019-03-31")=count(*)

1. 596. 超过5名学生的课

select class
from Courses
groupby class
havingcount(*)>=5

27.1729. 求关注者的数量

select user_id,count(*)as followers_count
from Followers
groupby user_id
orderby user_id

28.619. 只出现一次的最大数字

selectmax(num) num
from(select num
from MyNumbers
groupby num
havingcount(*)=1) num1

29.1045. 买下所有产品的客户

select customer_id
from Customer
groupby customer_id
havingcount(distinct product_key)=(selectcount(*)from Product)

30.1731. 每位经理的下属员工数量

select  e2.employee_id,e2.name,count(*)as reports_count,round(avg(e1.age),0)as average_age
from Employees e1 leftjoin Employees e2
on e1.reports_to = e2.employee_id
groupby e2.employee_id
having e2.employee_id isnotnullorderby employee_id

31.1789. 员工的直属部门

(select employee_id,department_id
from Employee
where primary_flag ='Y')UNION(select employee_id,department_id
from Employee
groupby  employee_id
havingcount(*)=1)orderby employee_id

32.610. 判断三角形

select*,(casewhen(x+y>z and x+z>y and z+y>x)then"Yes"else"No"end)as triangle
from Triangle

33.180. 连续出现的数字

selectdistinct L1.num as ConsecutiveNums
from Logs L1
join Logs L2 on L1.id=L2.id-1join Logs L3 on L2.id=L3.id-1where L1.num=L2.num and L2.num=L3.num

34.1164. 指定日期的产品价格

select product_id, new_price as price
from Products
where(product_id,change_date)in(select product_id,max(change_date)from Products
where change_date<="2019-08-16"groupby product_id)unionselect product_id,10as price
from Products
where product_id  notin(select product_id from Products where change_date<="2019-08-16")

⭐35.1204. 最后一个能进入巴士的人

select q1.person_name
from Queue q1
join Queue q2 on q1.turn>=q2.turn
groupby q1.person_id
havingsum(q2.weight)<=1000orderby q1.turn desclimit1

36.1907. 按分类统计薪水

select"Low Salary" category,count(*) accounts_count
from Accounts
where income<20000unionselect"Average Salary" category,count(*) accounts_count
from Accounts
where income between20000and50000unionselect"High Salary" category,count(*) accounts_count
from Accounts
where income>50000

37.1978. 上级经理已离职的公司员工

select employee_id
from Employees
where salary<30000and  manager_id notin(select employee_id from Employees)orderby employee_id

38.626. 换座位

select(casewhen id%2!=0and id!=(selectcount(*)from Seat)then id+1when id%2=0then id-1else id end)as id,student
from Seat
orderby id

39.1341. 电影评分

(select name as results
from Users u
join MovieRating r1 on u.user_id = r1.user_id 
groupby u.user_id
orderbycount(*)desc,name limit1)unionall(select title as results
from Movies m
join  MovieRating r2 on m.movie_id = r2.movie_id andleft(r2.created_at,7)="2020-02"groupby r2.movie_id 
orderbyavg(rating)desc,title limit1)

⭐⭐40.1321. 餐馆营业额变化增长

#注意join时where的用法以及分组之后avg函数的使用select a.visited_on,sum(c.amount)as amount,round((sum(c.amount))/7,2)as average_amount
from(selectdistinct visited_on 
from Customer)as a
leftjoin  customer c
on(c.visited_on>=a.visited_on -interval6day)and(c.visited_on<=a.visited_on)where a.visited_on>=(selectmin(visited_on)from customer)+6groupby a.visited_on
orderby a.visited_on

41.602. 好友申请 II ：谁有最多的好友

select a.id,count(*)as num
from(select requester_id as id from RequestAccepted r1
unionallselect accepter_id as id from RequestAccepted r2)as a
groupby id
orderby num desclimit1

42.585. 2016年的投资

selectround(sum(tiv_2016),2) tiv_2016
from Insurance
where tiv_2015 in(select tiv_2015 from Insurance 
groupby tiv_2015 havingcount(*)>1)and concat(lat, lon)in(select concat(lat, lon)from Insurance 
groupby concat(lat, lon)havingcount(*)=1)

⭐⭐⭐43.185. 部门工资前三高的所有员工

select d.name as  Department,e.name  as Employee,e.salary
from Employee e leftjoin Department d
on e.departmentId=d.id
where e.id in(select e1.id
from Employee e1 leftjoin  Employee e2
on e1.departmentId=e2.departmentId and e1.salary<e2.salary
groupby e1.id
havingcount(distinct e2.salary)<=2)

44.1667. 修复表中的名字

select user_id,concat(upper(left(name,1)),lower(SUBSTRING(name,2))) name
from Users
orderby user_id

45.1527. 患某种疾病的患者

select*from Patients
where conditions like"DIAB1%"or conditions like"% DIAB1%"

46.196. 删除重复的电子邮箱

deletefrom
Person
where id notin(select id from(selectmin(id) id
from Person
groupby email)as a)

47.176. 第二高的薪水

select ifnull((selectdistinct salary
from Employee
orderby  salary desclimit1offset1),null)as SecondHighestSalary

48.1484. 按日期分组销售产品

select sell_date,count(distinct product)as num_sold,
group_concat(distinct product orderby product SEPARATOR ',')as products
from Activities
groupby sell_date
orderby sell_date

49.1327. 列出指定时间段内所有的下单产品

select product_name,sum(unit)as unit
from Products p join Orders o
on p.product_id=o.product_id andleft(o.order_date,7)="2020-02"groupby product_name
havingsum(unit)>=100

50.1517. 查找拥有有效邮箱的用户

SELECT user_id, name, mail
FROM Users
WHERE mail REGEXP'^[a-zA-Z][a-zA-Z0-9_.-]*\\@leetcode\\.com$';

二、高频SQL50题（进阶版）

题目考查类型题号查询1-5连接6-11聚合函数12-19排序和分组20-26高级查询和连接27-35子查询36-43高级字符串函数 / 正则表达式 / 子句44-50
1.1821. 寻找今年具有正收入的客户

select customer_id
from Customers
whereyear=2021and revenue>0

2.183. 从不订购的客户

select name as Customers
from Customers
where id notin(select customerId from Orders)

1. 1873. 计算特殊奖金

select employee_id,salary*(casewhen employee_id%2!=0andleft(name,1)!="M"then1else0end)as bonus
from Employees
orderby employee_id

4.1398. 购买了产品 A 和产品 B 却没有购买产品 C 的顾客

# 方法一select customer_id,customer_name
from Customers
where customer_id in(select customer_id
from Orders
where product_name="A"or product_name="B"groupby customer_id
havingcount(distinct product_name)=2)and customer_id notin(select customer_id
from Orders
where product_name="C"groupby customer_id)# 方法二：巧用sumselect customer_id,customer_name
from Customers
where customer_id in(select customer_id
from Orders
groupby customer_id
havingsum(product_name="A")*sum(product_name="B")>0andsum(product_name="C")=0)

5.1112. 每位学生的最高成绩

select student_id,min(course_id) course_id,grade
from Enrollments
where(student_id,grade)in(select student_id,max(grade) grade
from Enrollments 
groupby student_id)# 注意这里的group by，因为取了min，所有要group bygroupby student_id
orderby student_id,course_id

6.175. 组合两个表

select firstName,lastName,city,state
from Person p leftjoin Address a on p.PersonId = a.personId

1. 1607. 没有卖出的卖家

select seller_name
from Seller s
where seller_id notin(select seller_id
from  Orders 
whereleft(sale_date,4)="2020")orderby seller_name

1. 1407. 排名靠前的旅行者

select name,ifnull(sum(r.distance),0) travelled_distance
from Users u
leftjoin Rides r on u.id=r.user_id
groupby u.id
orderby  travelled_distance desc,name

9.607. 销售员

select name
from SalesPerson
where sales_id notin(select sales_id
from Orders o  join Company c
on o.com_id = c.com_id and c.name="RED")

10.1440. 计算布尔表达式的值

select e.*,(casewhen operator=">"and v1.value>v2.valuethen"true"when operator="<"and v1.value<v2.valuethen"true"when operator="="and v1.value=v2.valuethen"true"else"false"end)asvaluefrom Expressions e
leftjoin Variables v1 on v1.name=e.left_operand
leftjoin Variables v2 on v2.name=e.right_operand

11.1212. 查询球队积分

select t.team_id,t.team_name,sum(casewhen m.host_team = t.team_id and host_goals>guest_goals then3when m.guest_team = t.team_id and host_goals<guest_goals then3when   host_goals=guest_goals then1else0end)as num_points
from Matches m
rightjoin Teams t  on m.host_team = t.team_id or m.guest_team=t.team_id
groupby t.team_id
orderby num_points desc,team_id

12.1890. 2020年最后一次登录

select user_id,max(time_stamp)as last_stamp
from Logins
whereleft(time_stamp,4)='2020'groupby user_id

13.511. 游戏玩法分析 I

select player_id,min(event_date)as first_login
from Activity
groupby player_id

14.1571. 仓库经理

select name as warehouse_name,sum(units*Width*Length*Height)as volume
from Warehouse w leftjoin Products p
on w.product_id=p.product_id
groupby w.name

15.586. 订单最多的客户

select customer_number
from Orders
groupby customer_number
orderbycount(customer_number)desclimit1

16.1741. 查找每个员工花费的总时间

select event_day asday,emp_id,sum(out_time-in_time)as total_time
from Employees
groupby event_day,emp_id

17.1173. 即时食物配送 I

selectround(100*sum(casewhen order_date=customer_pref_delivery_date then1else0end)/(count(delivery_id)),2)as immediate_percentage
from Delivery

18.1445. 苹果和桔子

select sale_date,sum(casewhen fruit="apples"then sold_num
                          else-sold_num
                          end)as diff 
from Sales
groupby sale_date

19.1699. 两人之间的通话次数

# 先找到适合的顺序select(casewhen from_id<to_id then from_id  else to_id end)as person1,(casewhen from_id<to_id then to_id  else from_id end)as person2,count(*)as call_count,sum(duration)as total_duration
from Calls
groupby person1,person2

20.1587. 银行账户概要 II

select  u.name as NAME,sum(t.amount)as BALANCE
fromTransactions t leftjoin Users u on t.account=u.account
groupby t.account
havingsum(amount)>10000

21.182. 查找重复的电子邮箱

select email Email
from Person
groupby email
havingcount(*)>=2

22.1050. 合作过至少三次的演员和导演

select actor_id,director_id
from ActorDirector
groupby actor_id,director_id
havingcount(*)>=3

23.1511. 消费者下单频率

select o.customer_id,c.name
from Orders o  join Product p on o.product_id=p.product_id
               join Customers c on o.customer_id=c.customer_id
groupby customer_id
havingsum(casewhenleft(o.order_date,7)="2020-06"then quantity*price else0end)>=100andsum(casewhenleft(o.order_date,7)="2020-07"then quantity*price else0end)>=100

24.1693. 每天的领导和合伙人

select date_id,make_name,count(distinct lead_id) unique_leads,count(distinct partner_id) unique_partners
from DailySales
groupby date_id,make_name

25.1495. 上月播放的儿童适宜电影

selectdistinct title
from Content c leftjoin TVProgram t on c.content_id =t.content_id 
where c.Kids_content='Y'andleft(t.program_date,7)="2020-06"and content_type="Movies"

26.1501. 可以放心投资的国家

select co.name as  country
from Person p join Country co onleft(p.phone_number,3)=co.country_code
              join Calls ca   on p.id=ca.caller_id or p.id=ca.callee_id
groupby co.country_code
havingavg(duration)>(selectavg(duration)from Calls)

27.603. 连续空余座位

selectdistinct c1.seat_id
from Cinema c1 join Cinema c2
on abs(c1.seat_id - c2.seat_id)=1where c1.free=1and c2.free=1orderby  c1.seat_id

28.1795. 每个产品在不同商店的价格

select product_id,"store1"as store,store1 price
from Products
where store1 isnotnullunionallselect product_id,"store2"as store,store2 price
from Products
where store2 isnotnullunionallselect product_id,"store3"as store,store3 price
from Products
where store3 isnotnull

29.613. 直线上的最近距离

selectmin(abs(p1.x-p2.x))as shortest
fromPoint p1  joinPoint p2 on p1.x!=p2.x

30.1965. 丢失信息的雇员

select employee_id
from Employees
where employee_id  notin(select employee_id from Salaries)unionselect employee_id
from Salaries
where employee_id  notin(select employee_id from Employees)orderby  employee_id

31.1264. 页面推荐

selectdistinct page_id as recommended_page
from Likes
where user_id in(select user2_id user_id
from Friendship
where user1_id ="1"unionselect user1_id user_id
from Friendship
where user2_id ="1")and page_id notin(select page_id from Likes where user_id=1)

32.608. 树节点

select id,(casewhen p_id isnullthen"Root"when id notin(select p_id from Tree where  p_id isnotnull)then"Leaf"else"Inner"end)typefrom Tree

33.534. 游戏玩法分析 III

SELECT a2.player_id,a2.event_date,sum(a1.games_played)as games_played_so_far
FROM Activity a1 leftjoin Activity a2  on a1.player_id=a2.player_id and a2.event_date>=a1.event_date
groupby a2.event_date,player_id

34.1783. 大满贯数量

select p.player_id,p.player_name,sum(c.Wimbledon=p.player_id)+sum(c.Fr_open=p.player_id)+sum(c.US_open=p.player_id)+sum(c.Au_open=p.player_id)grand_slams_count
from Players p,Championships c
groupby p.player_id
having grand_slams_count>0

35.1747. 应该被禁止的 Leetflex 账户

select account_id
from(select account_id,login,logout,lead (login,1)over()as ll,nums
from(select*,row_number()over(partitionby account_id orderby login)as nums
from loginfo)aaa)bbb
where nums=1and ll between login and logout

36.1350. 院系无效的学生

select id,name
from Students s 
where s.department_id notin(select id from Departments)

37.1303. 求团队人数

select e1.employee_id,count(*)as team_size
from Employee e1 leftjoin Employee e2 on e1.team_id=e2.team_id
groupby e1.employee_id

38.512. 游戏玩法分析 II

select  player_id,device_id
from Activity
where(player_id,event_date)in(select  player_id,min(event_date)from Activity
groupby player_id)

39.184. 部门工资最高的员工

select d.name Department,e.name Employee,e.salary
from  Employee e leftjoin Department d on e.departmentId=d.id 
where(e.departmentId,e.salary)in(select departmentId,max(salary)from  Employee  
groupby departmentId )

40.1549. 每件商品的最新订单

select p.product_name,o.product_id,o.order_id,o.order_date
from Orders o leftjoin Products p on o.product_id=p.product_id
where(o.product_id,o.order_date)in(select product_id,max(order_date)from Orders
groupby product_id)orderby p.product_name,o.product_id,o.order_id

41.1532. 最近的三笔订单

select c.name as customer_name,c.customer_id,o2.order_id,o2.order_date
from Orders o1 leftjoin Orders o2 on o1.customer_id=o2.customer_id and o1.order_date>=o2.order_date 
                leftjoin Customers c on o1.customer_id=c.customer_id
groupby o2.order_id
#比我日期更近的order_date不超过3个，所以是前三havingcount(o1.order_date)<=3orderby c.name,c.customer_id,o2.order_date desc

42.1831. 每天的最大交易

select transaction_id
fromTransactionswhere(day,amount)in(selectday,max(amount)fromTransactionsgroupbyday)orderby transaction_id

43.1077. 项目员工 III

# 解题思路同39题select p.project_id,p.employee_id
from Project p leftjoin Employee e on p.employee_id=e.employee_id
where(p.project_id,e.experience_years)in(select p.project_id,max(experience_years)from Project p leftjoin Employee e on p.employee_id=e.employee_id
groupby p.project_id)

44.1285. 找到连续区间的开始和结束数字

#开窗函数，还看不太懂selectmin(log_id)as start_id,max(log_id)as end_id
from(SELECT
    log_id,
    log_id - row_number()over() diff
FROM logs)as t
groupby diff

45.1596. 每位顾客最经常订购的商品

select o.customer_id,o.product_id,p.product_name
from(select customer_id,product_id,
rank()over(partitionby  customer_id orderbycount(product_id)desc)rnk 
from Orders
groupby customer_id,product_id)o
join products p on o.product_id=p.product_id
where rnk=1

46.1709. 访问日期之间最大的空档期

select user_id,max(datediff(next_day,visit_date))as  biggest_window
from(select user_id,visit_date,LEAD(visit_date,1,'2021-1-1')over(partitionby user_id orderby visit_date)as next_day
from  UserVisits)as tmp
groupby user_id
orderby user_id

47.1270. 向公司 CEO 汇报工作的所有人

selectdistinct employee_id
from(select employee_id from Employees where manager_id=1unionall(select employee_id from Employees
where manager_id  in(select employee_id from Employees where manager_id=1))unionallselect employee_id from Employees
where manager_id  in(select employee_id from Employees
where manager_id  in(select employee_id from Employees where manager_id=1)))e
where employee_id!=1SELECT e1.employee_id
FROM Employees e1
JOIN Employees e2 ON e1.manager_id = e2.employee_id
JOIN Employees e3 ON e2.manager_id = e3.employee_id
WHERE e1.employee_id !=1AND e3.manager_id =1

48.1412. 查找成绩处于中游的学生

select tmp.student_id,s.student_name
from(select*,if(dense_rank()over(partitionby exam_id orderby score desc)=1,1,0) d_rank,if(dense_rank()over(partitionby exam_id orderby score )=1,1,0) a_rank
from Exam)tmp
leftjoin Student s on tmp.student_id=s.student_id
groupby tmp.student_id
havingsum(d_rank)=0andsum(a_rank)=0orderby tmp.student_id

⭐⭐⭐49.1767. 寻找没有被执行的任务对

with recursive table1 as(select task_id,subtasks_count subtask_id 
from Tasks
unionallselect task_id,subtask_id-1from table1 where  subtask_id >1)select task_id,subtask_id
from table1
leftjoin Executed E using(task_id, subtask_id)where E.task_id isnull

⭐⭐⭐1225. 报告系统状态的连续日期

selecttypeas period_state,min(date)as start_date,max(date)as end_date
from(selecttype,date,subdate(date,row_number()over(partitionbytypeorderbydate))as diff
from(select"failed"astype,fail_date asdatefrom Failed
unionallselect"succeeded"astype,success_date  asdatefrom Succeeded)tmp1)tmp2
wheredatebetween"2019-01-01"and"2019-12-31"groupbytype,diff
orderby  start_date