LeetCode 高频 SQL 50 题（进阶版）
2023-06-05 15:04:59 3 举报AI智能生成
Leetcode sql
作者其他创作
大纲/内容
查询
1821. 寻找今年具有正收入的客户
select
customer_id
from Customers
where year = '2021' and revenue > 0;
customer_id
from Customers
where year = '2021' and revenue > 0;
183. 从不订购的客户
select
s.Name as 'Customers'
from Customers s
where s.Id not in (select o.CustomerId from Orders o)
;
s.Name as 'Customers'
from Customers s
where s.Id not in (select o.CustomerId from Orders o)
;
1873. 计算特殊奖金
select
employee_id
,case when mod(employee_id,2) = 1 and substr(name,1,1) <> 'M' then salary else 0 end as bonus
from Employees
order by employee_id;
employee_id
,case when mod(employee_id,2) = 1 and substr(name,1,1) <> 'M' then salary else 0 end as bonus
from Employees
order by employee_id;
1398. 购买了产品 A 和产品 B 却没有购买产品 C 的顾客
way1:傻瓜写法
select
o.customer_id,c.customer_name
from Orders o left join Customers c using(customer_id)
where
o.customer_id in (select customer_id from Orders where product_name ='A')
AND o.customer_id in (select customer_id from Orders where product_name ='B')
AND o.customer_id NOT in (select customer_id from Orders where product_name ='C')
group by o.customer_id order by o.customer_id
o.customer_id,c.customer_name
from Orders o left join Customers c using(customer_id)
where
o.customer_id in (select customer_id from Orders where product_name ='A')
AND o.customer_id in (select customer_id from Orders where product_name ='B')
AND o.customer_id NOT in (select customer_id from Orders where product_name ='C')
group by o.customer_id order by o.customer_id
way2:having 写法
select
customer_id
,customer_name
from Orders join Customers using(customer_id)
group by customer_id
having sum(product_name='A') and sum(product_name='B')
AND not sum(product_name='C')
order by customer_id
customer_id
,customer_name
from Orders join Customers using(customer_id)
group by customer_id
having sum(product_name='A') and sum(product_name='B')
AND not sum(product_name='C')
order by customer_id
way3：正则表达式
1112. 每位学生的最高成绩
way1:开窗函数
select
student_id,min(course_id) as course_id,grade
from (
select
student_id,course_id,grade
,dense_rank() over(partition by student_id order by grade desc) as rn
from Enrollments
) a where rn =1
group by student_id,grade
order by student_id;
student_id,min(course_id) as course_id,grade
from (
select
student_id,course_id,grade
,dense_rank() over(partition by student_id order by grade desc) as rn
from Enrollments
) a where rn =1
group by student_id,grade
order by student_id;
way2:子查询
# 子查询
select
student_id,min(course_id) as course_id
,grade
from Enrollments
where (student_id,grade) in (
select
student_id,max(grade)
from Enrollments
group by student_id
)
group by student_id,grade
order by student_id
select
student_id,min(course_id) as course_id
,grade
from Enrollments
where (student_id,grade) in (
select
student_id,max(grade)
from Enrollments
group by student_id
)
group by student_id,grade
order by student_id
连接
175. 组合两个表
select
p.firstName
,p.lastName
,a.city
,a.state
from Person p left join Address a on p.personId = a.personId
;
p.firstName
,p.lastName
,a.city
,a.state
from Person p left join Address a on p.personId = a.personId
;
1607. 没有卖出的卖家
# 写一个SQL语句, 报告所有在2020年度没有任何卖出的卖家的名字.
# 返回结果按照 seller_name 升序排列.
select
s.seller_name as 'seller_name'
from Seller s
where s.seller_id not in (
select seller_id from Orders where year(sale_date) = '2020'
)
order by s.seller_name;
# 返回结果按照 seller_name 升序排列.
select
s.seller_name as 'seller_name'
from Seller s
where s.seller_id not in (
select seller_id from Orders where year(sale_date) = '2020'
)
order by s.seller_name;
select
s.seller_name
from Seller s
left join Orders o on s.seller_id = o.seller_id and year(o.sale_date) = '2020'
where o.seller_id is null
order by s.seller_name
;
s.seller_name
from Seller s
left join Orders o on s.seller_id = o.seller_id and year(o.sale_date) = '2020'
where o.seller_id is null
order by s.seller_name
;
1407. 排名靠前的旅行者
select
u.name,ifnull(sum(r.distance),0) as travelled_distance
from Users u left join Rides r
on u.id = r.user_id
group by r.user_id
order by travelled_distance desc,u.name asc;
u.name,ifnull(sum(r.distance),0) as travelled_distance
from Users u left join Rides r
on u.id = r.user_id
group by r.user_id
order by travelled_distance desc,u.name asc;
-- 性能低、拆解
select
u.name,ifnull(travelled_distance,0) as travelled_distance
from Users u left join
(select user_id,sum(distance) as travelled_distance
from Rides group by user_id) r
on u.id = r.user_id
group by r.user_id
order by travelled_distance desc,u.name asc;
select
u.name,ifnull(travelled_distance,0) as travelled_distance
from Users u left join
(select user_id,sum(distance) as travelled_distance
from Rides group by user_id) r
on u.id = r.user_id
group by r.user_id
order by travelled_distance desc,u.name asc;
607. 销售员
select
name
from SalesPerson
where sales_id not in (
select sales_id
from Orders o left join Company c on o.com_id = c.com_id
where c.name = 'red'
);
name
from SalesPerson
where sales_id not in (
select sales_id
from Orders o left join Company c on o.com_id = c.com_id
where c.name = 'red'
);
1440. 计算布尔表达式的值
# Write your MySQL query statement below
SELECT E.left_operand AS left_operand,
E.operator AS operator,
E.right_operand AS right_operand,
-- ② 再判断这一行要比的大小
( CASE
-- ③ 若 这一组的正确答案 和 E表中对这一组的判断 一样，就返回 true
WHEN V1.value > V2.value AND E.operator = '>' THEN 'true'
WHEN V1.value = V2.value AND E.operator = '=' THEN 'true'
WHEN V1.value < V2.value AND E.operator = '<' THEN 'true'
-- ④ 其他回答则是 false
ELSE 'false'
END
) AS value
FROM Expressions E
-- ① 先把 x、y 的值填进表中（以增广的形式）
LEFT JOIN Variables V1
ON E.left_operand = V1.name
LEFT JOIN Variables V2
ON E.right_operand = V2.name
SELECT E.left_operand AS left_operand,
E.operator AS operator,
E.right_operand AS right_operand,
-- ② 再判断这一行要比的大小
( CASE
-- ③ 若 这一组的正确答案 和 E表中对这一组的判断 一样，就返回 true
WHEN V1.value > V2.value AND E.operator = '>' THEN 'true'
WHEN V1.value = V2.value AND E.operator = '=' THEN 'true'
WHEN V1.value < V2.value AND E.operator = '<' THEN 'true'
-- ④ 其他回答则是 false
ELSE 'false'
END
) AS value
FROM Expressions E
-- ① 先把 x、y 的值填进表中（以增广的形式）
LEFT JOIN Variables V1
ON E.left_operand = V1.name
LEFT JOIN Variables V2
ON E.right_operand = V2.name
1212. 查询球队积分
select
team_id,team_name,ifnull(sum(num_points),0) as num_points
from (
## 主场
select
t.team_id
,t.team_name
,case
when host_goals > guest_goals then 3
when host_goals = guest_goals then 1
when host_goals < guest_goals then 0
end as num_points
from Matches m
right join Teams t
on host_team = team_id
union all3
## 客场
select
t.team_id
,t.team_name
,case
when guest_goals > host_goals then 3
when guest_goals = host_goals then 1
when guest_goals < host_goals then 0
end as num_points
from Matches m
right join Teams t
on guest_team = team_id
) a
group by team_id,team_id
order by num_points desc,team_id asc
team_id,team_name,ifnull(sum(num_points),0) as num_points
from (
## 主场
select
t.team_id
,t.team_name
,case
when host_goals > guest_goals then 3
when host_goals = guest_goals then 1
when host_goals < guest_goals then 0
end as num_points
from Matches m
right join Teams t
on host_team = team_id
union all3
## 客场
select
t.team_id
,t.team_name
,case
when guest_goals > host_goals then 3
when guest_goals = host_goals then 1
when guest_goals < host_goals then 0
end as num_points
from Matches m
right join Teams t
on guest_team = team_id
) a
group by team_id,team_id
order by num_points desc,team_id asc
select
team_id,team_name,ifnull(sum(num_points),0) as num_points
from (
## 主场
select
host_team as team
,case
when host_goals > guest_goals then 3
when host_goals = guest_goals then 1
when host_goals < guest_goals then 0
end as num_points
from Matches m
union all
## 客场
select
guest_team as team
,case
when guest_goals > host_goals then 3
when guest_goals = host_goals then 1
when guest_goals < host_goals then 0
end as num_points
from Matches m
) a
right join Teams t
on team = t.team_id
group by t.team_id,t.team_name
order by num_points desc,team_id asc
team_id,team_name,ifnull(sum(num_points),0) as num_points
from (
## 主场
select
host_team as team
,case
when host_goals > guest_goals then 3
when host_goals = guest_goals then 1
when host_goals < guest_goals then 0
end as num_points
from Matches m
union all
## 客场
select
guest_team as team
,case
when guest_goals > host_goals then 3
when guest_goals = host_goals then 1
when guest_goals < host_goals then 0
end as num_points
from Matches m
) a
right join Teams t
on team = t.team_id
group by t.team_id,t.team_name
order by num_points desc,team_id asc
聚合函数
1890. 2020年最后一次登录
select
user_id
,max(time_stamp) as last_stamp
from Logins
where year(time_stamp) = '2020'
group by user_id
user_id
,max(time_stamp) as last_stamp
from Logins
where year(time_stamp) = '2020'
group by user_id
511. 游戏玩法分析 I
select
player_id
,min(event_date) as first_login
from Activity
group by player_id;
player_id
,min(event_date) as first_login
from Activity
group by player_id;
1571. 仓库经理
select
w.name as 'WAREHOUSE_NAME'
,sum(w.units * (Width * Length * Height)) as 'VOLUME'
from Warehouse w,Products pp where w.product_id = pp.product_id
group by w.name;
w.name as 'WAREHOUSE_NAME'
,sum(w.units * (Width * Length * Height)) as 'VOLUME'
from Warehouse w,Products pp where w.product_id = pp.product_id
group by w.name;
select
w.name as 'WAREHOUSE_NAME'
,sum(w.units * (Width * Length * Height)) as 'VOLUME'
from Warehouse w left join Products pp on w.product_id = pp.product_id
group by w.name;
w.name as 'WAREHOUSE_NAME'
,sum(w.units * (Width * Length * Height)) as 'VOLUME'
from Warehouse w left join Products pp on w.product_id = pp.product_id
group by w.name;
586. 订单最多的客户
way1:开窗函数
select
a.customer_number
from (
select customer_number
,dense_rank() over(order by count(order_number) desc) as rn
from Orders
group by customer_number
) a
where a.rn = '1';
a.customer_number
from (
select customer_number
,dense_rank() over(order by count(order_number) desc) as rn
from Orders
group by customer_number
) a
where a.rn = '1';
way2：官方解法
select
customer_number
from Orders
group by customer_number
order by count(*) desc
limit 1;
customer_number
from Orders
group by customer_number
order by count(*) desc
limit 1;
1741. 查找每个员工花费的总时间
编写一个SQL查询以计算每位员工每天在办公室花费的总时间（以分钟为单位）。
请注意，在一天之内，同一员工是可以多次进入和离开办公室的。
在办公室里一次进出所花费的时间为out_time 减去 in_time。
请注意，在一天之内，同一员工是可以多次进入和离开办公室的。
在办公室里一次进出所花费的时间为out_time 减去 in_time。
select
event_day as day
,emp_id
,sum(out_time-in_time) as total_time
from Employees
group by event_day,emp_id
event_day as day
,emp_id
,sum(out_time-in_time) as total_time
from Employees
group by event_day,emp_id
1173. 即时食物配送 I
如果顾客期望的配送日期和下单日期相同，则该订单称为 「即时订单」，否则称为「计划订单」。
写一条 SQL 查询语句获取即时订单所占的百分比， 保留两位小数。
查询结果如下所示。
写一条 SQL 查询语句获取即时订单所占的百分比， 保留两位小数。
查询结果如下所示。
way1
select
round(ifnull((
(sum(case when order_date = customer_pref_delivery_date then 1 else 0 end))
/(sum(1)) * 100
),0),2) as immediate_percentage
from Delivery;
round(ifnull((
(sum(case when order_date = customer_pref_delivery_date then 1 else 0 end))
/(sum(1)) * 100
),0),2) as immediate_percentage
from Delivery;
way2
select
round(
sum(order_date = customer_pref_delivery_date)
/count(*) * 100
,2) as immediate_percentage
from Delivery
;
round(
sum(order_date = customer_pref_delivery_date)
/count(*) * 100
,2) as immediate_percentage
from Delivery
;
way3
select
round(
(select count(*) from Delivery where order_date = customer_pref_delivery_date)
/(select count(*) from Delivery)
* 100
,2) as immediate_percentage;
round(
(select count(*) from Delivery where order_date = customer_pref_delivery_date)
/(select count(*) from Delivery)
* 100
,2) as immediate_percentage;
1445. 苹果和桔子
way1
select
sale_date
,(sum(case when fruit = 'apples' then sold_num else 0 end)
-sum(case when fruit = 'oranges' then sold_num else 0 end)) as diff
from Sales
group by sale_date
order by sale_date
sale_date
,(sum(case when fruit = 'apples' then sold_num else 0 end)
-sum(case when fruit = 'oranges' then sold_num else 0 end)) as diff
from Sales
group by sale_date
order by sale_date
way2
select
sale_date
,(sum(case when fruit = 'apples' then sold_num else -sold_num end)) as diff
from Sales
group by sale_date
order by sale_date
sale_date
,(sum(case when fruit = 'apples' then sold_num else -sold_num end)) as diff
from Sales
group by sale_date
order by sale_date
way3:自连接
select
s1.sale_date
,(s2.sold_num-s1.sold_num) as diff
from Sales s1, Sales s2
where s1.sale_date =s2.sale_date
and s1.fruit != s2.fruit
group by s1.sale_date
s1.sale_date
,(s2.sold_num-s1.sold_num) as diff
from Sales s1, Sales s2
where s1.sale_date =s2.sale_date
and s1.fruit != s2.fruit
group by s1.sale_date
select
s1.sale_date
,(s2.sold_num-s1.sold_num) as diff
from Sales s1 inner join Sales s2
on s1.sale_date =s2.sale_date
where s1.fruit != s2.fruit
group by s1.sale_date
s1.sale_date
,(s2.sold_num-s1.sold_num) as diff
from Sales s1 inner join Sales s2
on s1.sale_date =s2.sale_date
where s1.fruit != s2.fruit
group by s1.sale_date
1699. 两人之间的通话次数
## way1 位置调换
select
case when from_id > to_id then to_id else from_id end as person1
,case when from_id > to_id then from_id else to_id end as person2
,count(duration) as call_count
,sum(duration) as total_duration
from Calls
group by person1,person2
select
case when from_id > to_id then to_id else from_id end as person1
,case when from_id > to_id then from_id else to_id end as person2
,count(duration) as call_count
,sum(duration) as total_duration
from Calls
group by person1,person2
排序和分组
1587. 银行账户概要 II
select
u.name
,sum(amount) as balance
from Transactions t left join Users u on t.account = u.account
group by t.account
having balance > 10000;
u.name
,sum(amount) as balance
from Transactions t left join Users u on t.account = u.account
group by t.account
having balance > 10000;
select
*from (select
u.name
,sum(amount) as balance
from Transactions t left join Users u on t.account = u.account
group by t.account) a
where a.balance >10000
;
*from (select
u.name
,sum(amount) as balance
from Transactions t left join Users u on t.account = u.account
group by t.account) a
where a.balance >10000
;
182. 查找重复的电子邮箱
way1:having
select
email as Email
from Person
where email is not null
group by email
having count(Email) >1;
email as Email
from Person
where email is not null
group by email
having count(Email) >1;
way1:where
select
email
from (select
email as Email
,count(email) as num
from Person
where email is not null
group by email) a
where a.num >1
;
from (select
email as Email
,count(email) as num
from Person
where email is not null
group by email) a
where a.num >1
;
1050. 合作过至少三次的演员和导演
way1：嵌套一层效率更高
select
actor_id,director_id from (select
actor_id,director_id
,count(timestamp) as cnt
from ActorDirector
group by actor_id,director_id
having cnt >= 3) a
;
actor_id,director_id from (select
actor_id,director_id
,count(timestamp) as cnt
from ActorDirector
group by actor_id,director_id
having cnt >= 3) a
;
way2：基本解法
select
actor_id,director_id
from ActorDirector
group by actor_id,director_id
having count(*) >= 3;
actor_id,director_id
from ActorDirector
group by actor_id,director_id
having count(*) >= 3;
way3:where
select
actor_id
,director_id
from (select
actor_id
,director_id
,count(timestamp) as cnt
from ActorDirector
group by actor_id
,director_id) a where a.cnt >=3
actor_id
,director_id
from (select
actor_id
,director_id
,count(timestamp) as cnt
from ActorDirector
group by actor_id
,director_id) a where a.cnt >=3
1511. 消费者下单频率
select
o.customer_id
,c.name
from Orders o left join Product p on o.product_id = p.product_id
left join Customers c on o.customer_id = c.customer_id
where order_date like '2020-07%' or order_date like '2020-06%'
group by o.customer_id,c.name
having sum(case when order_date like '2020-07%' then p.price*o.quantity else 0 end ) >= 100
and sum(case when order_date like '2020-06%' then p.price*o.quantity else 0 end ) >= 100
o.customer_id
,c.name
from Orders o left join Product p on o.product_id = p.product_id
left join Customers c on o.customer_id = c.customer_id
where order_date like '2020-07%' or order_date like '2020-06%'
group by o.customer_id,c.name
having sum(case when order_date like '2020-07%' then p.price*o.quantity else 0 end ) >= 100
and sum(case when order_date like '2020-06%' then p.price*o.quantity else 0 end ) >= 100
select
aa.customer_id
,aa.name
from (
select
o.customer_id
,c.name
,sum(case when order_date like '2020-07%' then p.price*o.quantity else 0 end ) as july
,sum(case when order_date like '2020-06%' then p.price*o.quantity else 0 end ) as june
from Orders o left join Product p on o.product_id = p.product_id
left join Customers c on o.customer_id = c.customer_id
where order_date like '2020-07%' or order_date like '2020-06%'
group by o.customer_id,c.name
) aa
where aa.july >= 100 and aa.june >= 100;
aa.customer_id
,aa.name
from (
select
o.customer_id
,c.name
,sum(case when order_date like '2020-07%' then p.price*o.quantity else 0 end ) as july
,sum(case when order_date like '2020-06%' then p.price*o.quantity else 0 end ) as june
from Orders o left join Product p on o.product_id = p.product_id
left join Customers c on o.customer_id = c.customer_id
where order_date like '2020-07%' or order_date like '2020-06%'
group by o.customer_id,c.name
) aa
where aa.july >= 100 and aa.june >= 100;
select c.customer_id,c.name
from Customers c
inner join (
select customer_id
from Orders o
left join Product p
on o.product_id=p.product_id
where order_date like '2020-06%'
group by o.customer_id
having sum(p.price*quantity)>=100
) as m6
on c.customer_id=m6.customer_id
inner join (
select customer_id,order_id
from Orders d
left join Product t
on d.product_id=t.product_id
where order_date like '2020-07%'
group by d.customer_id
having sum(t.price*quantity)>=100
) as m7
on c.customer_id=m7.customer_id
from Customers c
inner join (
select customer_id
from Orders o
left join Product p
on o.product_id=p.product_id
where order_date like '2020-06%'
group by o.customer_id
having sum(p.price*quantity)>=100
) as m6
on c.customer_id=m6.customer_id
inner join (
select customer_id,order_id
from Orders d
left join Product t
on d.product_id=t.product_id
where order_date like '2020-07%'
group by d.customer_id
having sum(t.price*quantity)>=100
) as m7
on c.customer_id=m7.customer_id
1693. 每天的领导和合伙人
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
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
1495. 上月播放的儿童适宜电影
select
distinct c.title as title
from TVProgram t left join Content c using(content_id)
where c.content_type = 'Movies' and DATE_FORMAT(t.program_date,'%Y-%m') = '2020-06'
and Kids_content = 'Y'
;
distinct c.title as title
from TVProgram t left join Content c using(content_id)
where c.content_type = 'Movies' and DATE_FORMAT(t.program_date,'%Y-%m') = '2020-06'
and Kids_content = 'Y'
;
考察10种“2020年6月”的表达
DATE_FORMAT(t.program_date,'%Y-%m') = '2020-06'
t.program_date like '2020-06%'
t.program_date regexp '^2020-06'
and year(t.program_date) = '2020' and month(t.program_date) ='06'
left(t.program_date,7) ='2020-06'
and extract(YEAR_MONTH from t.program_date) ='202006'
1501. 可以放心投资的国家
with aa as (select
caller_id as id
,duration
from Calls c1
union all
select
callee_id as id
,duration
from Calls c2)
,bb as (select
c.name as country
,p.id
from Person p
left join Country c on
substr(p.phone_number,1,3)=country_code)
select
bb.country
from aa left join bb using(id)
group by bb.country
having (sum(aa.duration)/count(*)) > (select sum(aa.duration)/count(*)from aa)
caller_id as id
,duration
from Calls c1
union all
select
callee_id as id
,duration
from Calls c2)
,bb as (select
c.name as country
,p.id
from Person p
left join Country c on
substr(p.phone_number,1,3)=country_code)
select
bb.country
from aa left join bb using(id)
group by bb.country
having (sum(aa.duration)/count(*)) > (select sum(aa.duration)/count(*)from aa)
with aa as (select
caller_id as id
,duration
from Calls c1
union all
select
callee_id as id
,duration
from Calls c2)
,bb as (select
c.name as country
,p.id
from Person p
left join Country c on
substr(p.phone_number,1,3)=country_code)
select
bb.country
from aa left join bb using(id)
group by bb.country
having avg(duration) > (select avg(aa.duration) from aa)
caller_id as id
,duration
from Calls c1
union all
select
callee_id as id
,duration
from Calls c2)
,bb as (select
c.name as country
,p.id
from Person p
left join Country c on
substr(p.phone_number,1,3)=country_code)
select
bb.country
from aa left join bb using(id)
group by bb.country
having avg(duration) > (select avg(aa.duration) from aa)
高级查询和连接
603. 连续空余座位
way1：将两个表连接的结果是这两个表的 笛卡尔乘积
select
distinct a.seat_id
from Cinema a,Cinema b
where abs(a.seat_id-b.seat_id)=1 and a.free=1 and b.free = 1
order by a.seat_id;
distinct a.seat_id
from Cinema a,Cinema b
where abs(a.seat_id-b.seat_id)=1 and a.free=1 and b.free = 1
order by a.seat_id;
way2：开窗函数
with aa as(
select seat_id,(seat_id - row_number() over()) as rn
from Cinema where free = '1'
order by seat_id
)
select
seat_id
from aa where rn in (
select rn
from aa
group by rn having count(*) >= 2
)
;
select seat_id,(seat_id - row_number() over()) as rn
from Cinema where free = '1'
order by seat_id
)
select
seat_id
from aa where rn in (
select rn
from aa
group by rn having count(*) >= 2
)
;
1795. 每个产品在不同商店的价格
way1:行转列（横表转竖表）
select
product_id
,'store1' as store
,store1 as price
from Products
where store1 is not null
union all
select
product_id
,'store2' as store
,store2 as price
from Products
where store2 is not null
union all
select
product_id
,'store3' as store
,store3 as price
from Products
where store3 is not null
product_id
,'store1' as store
,store1 as price
from Products
where store1 is not null
union all
select
product_id
,'store2' as store
,store2 as price
from Products
where store2 is not null
union all
select
product_id
,'store3' as store
,store3 as price
from Products
where store3 is not null
613. 直线上的最近距离
select
min(abs(a.x-b.x)) as shortest
from point a,point b
where a.x != b.x;
min(abs(a.x-b.x)) as shortest
from point a,point b
where a.x != b.x;
SELECT
MIN(ABS(p1.x - p2.x)) AS shortest
FROM
point p1
JOIN
point p2 ON p1.x != p2.x
;
MIN(ABS(p1.x - p2.x)) AS shortest
FROM
point p1
JOIN
point p2 ON p1.x != p2.x
;
1965. 丢失信息的雇员
way1:子查询
with aa as (
select employee_id from Employees
union all
select employee_id from Salaries
)
select
employee_id
from aa group by 1 having count(*) = 1 order by 1
select employee_id from Employees
union all
select employee_id from Salaries
)
select
employee_id
from aa group by 1 having count(*) = 1 order by 1
way2：union all
select employee_id from Employees where employee_id not in (select employee_id from Salaries)
union all
select employee_id from Salaries where employee_id not in (select employee_id from Employees)
order by employee_id
union all
select employee_id from Salaries where employee_id not in (select employee_id from Employees)
order by employee_id
1264. 页面推荐
SELECT DISTINCT page_id AS recommended_page
FROM Likes
WHERE user_id IN (
SELECT (
CASE
WHEN user1_id = 1 then user2_id
WHEN user2_id = 1 then user1_id
END
) AS user_id
FROM Friendship
WHERE user1_id = 1 OR user2_id = 1
) AND page_id NOT IN (
SELECT page_id FROM Likes WHERE user_id = 1
)
FROM Likes
WHERE user_id IN (
SELECT (
CASE
WHEN user1_id = 1 then user2_id
WHEN user2_id = 1 then user1_id
END
) AS user_id
FROM Friendship
WHERE user1_id = 1 OR user2_id = 1
) AND page_id NOT IN (
SELECT page_id FROM Likes WHERE user_id = 1
)
SELECT DISTINCT page_id AS recommended_page
FROM Likes
WHERE user_id IN (
SELECT user1_id AS user_id FROM Friendship WHERE user2_id = 1
UNION ALL
SELECT user2_id AS user_id FROM Friendship WHERE user1_id = 1
) AND page_id NOT IN (
SELECT page_id FROM Likes WHERE user_id = 1
)
FROM Likes
WHERE user_id IN (
SELECT user1_id AS user_id FROM Friendship WHERE user2_id = 1
UNION ALL
SELECT user2_id AS user_id FROM Friendship WHERE user1_id = 1
) AND page_id NOT IN (
SELECT page_id FROM Likes WHERE user_id = 1
)
608. 树节点
way1：
# Write your MySQL query statement below
SELECT
id, 'Root' AS Type
FROM
tree
WHERE
p_id IS NULL
UNION
SELECT
id, 'Leaf' AS Type
FROM
tree
WHERE
id NOT IN (SELECT DISTINCT
p_id
FROM
tree
WHERE
p_id IS NOT NULL)
AND p_id IS NOT NULL
UNION
SELECT
id, 'Inner' AS Type
FROM
tree
WHERE
id IN (SELECT DISTINCT
p_id
FROM
tree
WHERE
p_id IS NOT NULL)
AND p_id IS NOT NULL
ORDER BY id;
SELECT
id, 'Root' AS Type
FROM
tree
WHERE
p_id IS NULL
UNION
SELECT
id, 'Leaf' AS Type
FROM
tree
WHERE
id NOT IN (SELECT DISTINCT
p_id
FROM
tree
WHERE
p_id IS NOT NULL)
AND p_id IS NOT NULL
UNION
SELECT
id, 'Inner' AS Type
FROM
tree
WHERE
id IN (SELECT DISTINCT
p_id
FROM
tree
WHERE
p_id IS NOT NULL)
AND p_id IS NOT NULL
ORDER BY id;
way2
SELECT
id AS `Id`,
CASE
WHEN tree.id = (SELECT atree.id FROM tree atree WHERE atree.p_id IS NULL)
THEN 'Root'
WHEN tree.id IN (SELECT atree.p_id FROM tree atree)
THEN 'Inner'
ELSE 'Leaf'
END AS Type
FROM
tree
ORDER BY `Id`
;
id AS `Id`,
CASE
WHEN tree.id = (SELECT atree.id FROM tree atree WHERE atree.p_id IS NULL)
THEN 'Root'
WHEN tree.id IN (SELECT atree.p_id FROM tree atree)
THEN 'Inner'
ELSE 'Leaf'
END AS Type
FROM
tree
ORDER BY `Id`
;
way3
SELECT
atree.id,
IF(ISNULL(atree.p_id),
'Root',
IF(atree.id IN (SELECT p_id FROM tree), 'Inner','Leaf')) Type
FROM
tree atree
ORDER BY atree.id
atree.id,
IF(ISNULL(atree.p_id),
'Root',
IF(atree.id IN (SELECT p_id FROM tree), 'Inner','Leaf')) Type
FROM
tree atree
ORDER BY atree.id
534. 游戏玩法分析 III
way1:开窗函数
select
player_id
,event_date
,sum(games_played) over(partition by player_id order by event_date) as games_played_so_far
from Activity
player_id
,event_date
,sum(games_played) over(partition by player_id order by event_date) as games_played_so_far
from Activity
way2：自关联
select
a.player_id
,a.event_date
,sum(b.games_played) as games_played_so_far
from Activity a,Activity b
where a.player_id=b.player_id
and a.event_date>=b.event_date
group by a.player_id
,a.event_date
a.player_id
,a.event_date
,sum(b.games_played) as games_played_so_far
from Activity a,Activity b
where a.player_id=b.player_id
and a.event_date>=b.event_date
group by a.player_id
,a.event_date
1783. 大满贯数量
way1:行列转换
Wimbledon as id
from Championships a1
union all
select
Fr_open as id
from Championships a2
union all
select
US_open as id
from Championships a3
union all
select
Au_open as id
from Championships a4)
select
id as player_id
,bb.player_name
,count(id) as grand_slams_count
from aa inner join Players bb on aa.id = bb.player_id
group by id,bb.player_name
from Championships a1
union all
select
Fr_open as id
from Championships a2
union all
select
US_open as id
from Championships a3
union all
select
Au_open as id
from Championships a4)
select
id as player_id
,bb.player_name
,count(id) as grand_slams_count
from aa inner join Players bb on aa.id = bb.player_id
group by id,bb.player_name
way2：笛卡尔积
select
player_id
,player_name
,sum(
if(c.Wimbledon=p.player_id,1,0)+
if(c.Fr_open=p.player_id,1,0)+
if(c.US_open=p.player_id,1,0)+
if(c.Au_open=p.player_id,1,0)
) as grand_slams_count
from Players p ,Championships c
group by player_id,player_name
having grand_slams_count >0
player_id
,player_name
,sum(
if(c.Wimbledon=p.player_id,1,0)+
if(c.Fr_open=p.player_id,1,0)+
if(c.US_open=p.player_id,1,0)+
if(c.Au_open=p.player_id,1,0)
) as grand_slams_count
from Players p ,Championships c
group by player_id,player_name
having grand_slams_count >0
1747. 应该被禁止的 Leetflex 账户
way1：自连接
select
distinct a.account_id
from LogInfo a inner join LogInfo b
on a.account_id= b.account_id
and a.ip_address <> b.ip_address
and a.login between b.login and b.logout
distinct a.account_id
from LogInfo a inner join LogInfo b
on a.account_id= b.account_id
and a.ip_address <> b.ip_address
and a.login between b.login and b.logout
备注
a,b where
=
a inner join b on
子查询
1350. 院系无效的学生
select
s.id,s.name
from Students s left join Departments d on s.department_id = d.id
where d.name is null;
s.id,s.name
from Students s left join Departments d on s.department_id = d.id
where d.name is null;
select
s.id,s.name
from Students s
where s.department_id not in (
select id from Departments
);
s.id,s.name
from Students s
where s.department_id not in (
select id from Departments
);
1303. 求团队人数
way1
窗口函数
select
employee_id
,count(employee_id) over(partition by team_id) as team_size
from Employee
order by employee_id
;
employee_id
,count(employee_id) over(partition by team_id) as team_size
from Employee
order by employee_id
;
way2
自连接
select
a.employee_id,b.team_size
from Employee a,(select team_id
,count(team_id) as team_size
from Employee
group by team_id
order by team_id
) b
where a.team_id = b.team_id
order by a.employee_id
;
a.employee_id,b.team_size
from Employee a,(select team_id
,count(team_id) as team_size
from Employee
group by team_id
order by team_id
) b
where a.team_id = b.team_id
order by a.employee_id
;
512. 游戏玩法分析 II
select player_id
,device_id
from Activity ac
where (ac.player_id,ac.event_date) in (select player_id,
min(event_date) as mi
from Activity
group by player_id
)
,device_id
from Activity ac
where (ac.player_id,ac.event_date) in (select player_id,
min(event_date) as mi
from Activity
group by player_id
)
窗口函数
select
player_id,device_id
from (
select
player_id,device_id,
dense_rank() over(partition by player_id order by event_date asc) as rn
from Activity
) a where a.rn =1;
player_id,device_id
from (
select
player_id,device_id,
dense_rank() over(partition by player_id order by event_date asc) as rn
from Activity
) a where a.rn =1;
184. 部门工资最高的员工
way1：开窗函数
select
Department,Employee,Salary
from (select
d.name as Department
,e.name as Employee
,e.salary as Salary
,dense_rank() over(partition by e.departmentId order by e.salary desc) as rn
from Employee e left join Department d on e.departmentId = d.id
group by d.name
,e.name,e.salary
) aa
where rn = 1;
Department,Employee,Salary
from (select
d.name as Department
,e.name as Employee
,e.salary as Salary
,dense_rank() over(partition by e.departmentId order by e.salary desc) as rn
from Employee e left join Department d on e.departmentId = d.id
group by d.name
,e.name,e.salary
) aa
where rn = 1;
way2：子查询
select
Department.name as Department
,Employee.name as Employee
,Employee.Salary
from Employee
left join Department on Employee.departmentId = Department.id
where (departmentId,salary) in (
select
departmentId
,max(salary) as salary
from Employee
group by departmentId
)
Department.name as Department
,Employee.name as Employee
,Employee.Salary
from Employee
left join Department on Employee.departmentId = Department.id
where (departmentId,salary) in (
select
departmentId
,max(salary) as salary
from Employee
group by departmentId
)
1549. 每件商品的最新订单
way1：开窗函数
# Write your MySQL query statement below
select
product_name,product_id,order_id
,order_date
from (select
p.product_name
,s.product_id
,s.order_id
,s.order_date
,dense_rank() over(partition by s.product_id order by s.order_date desc) as rn
from Orders s left join Products p using(product_id)
) a
where rn =1
order by product_name,product_id,order_id
select
product_name,product_id,order_id
,order_date
from (select
p.product_name
,s.product_id
,s.order_id
,s.order_date
,dense_rank() over(partition by s.product_id order by s.order_date desc) as rn
from Orders s left join Products p using(product_id)
) a
where rn =1
order by product_name,product_id,order_id
way1:子查询
select
p.product_name,o.product_id,o.order_id,o.order_date
from Orders o inner join Products P using(product_id)
where (o.product_id,o.order_date) in (
select
product_id,max(order_date)
from Orders group by product_id
)
order by p.product_name,o.product_id,o.order_id
p.product_name,o.product_id,o.order_id,o.order_date
from Orders o inner join Products P using(product_id)
where (o.product_id,o.order_date) in (
select
product_id,max(order_date)
from Orders group by product_id
)
order by p.product_name,o.product_id,o.order_id
1532. 最近的三笔订单
way1:窗口函数
select
customer_name
,customer_id
,order_id
,order_date
from (select
c.name as customer_name
,s.customer_id
,s.order_id
,s.order_date
,rank() over(partition by s.customer_id order by s.order_date desc ) as rn
from Orders s left join Customers c using(customer_id)
) a where rn <=3
order by customer_name,customer_id,order_date desc
customer_name
,customer_id
,order_id
,order_date
from (select
c.name as customer_name
,s.customer_id
,s.order_id
,s.order_date
,rank() over(partition by s.customer_id order by s.order_date desc ) as rn
from Orders s left join Customers c using(customer_id)
) a where rn <=3
order by customer_name,customer_id,order_date desc
1831. 每天的最大交易
way1:开窗函数
select
transaction_id
from (
select
transaction_id
,dense_rank() over(partition by date_format(day,'%Y-%m-%d') order by amount desc) as rn
from Transactions
) a
where rn =1
order by transaction_id
transaction_id
from (
select
transaction_id
,dense_rank() over(partition by date_format(day,'%Y-%m-%d') order by amount desc) as rn
from Transactions
) a
where rn =1
order by transaction_id
way2:子查询
select
transaction_id
from Transactions
where (date(day),amount) in (
select
date(day)
,max(amount)
from Transactions
group by date(day)
)
order by transaction_id
transaction_id
from Transactions
where (date(day),amount) in (
select
date(day)
,max(amount)
from Transactions
group by date(day)
)
order by transaction_id
高级主题：窗口函数和公共表表达式（CTE）
1077. 项目员工 III
way1:开窗函数
select
project_id,employee_id
from (select
project_id
,employee_id
,dense_rank() over(partition by project_id order by experience_years desc) rn
from Project p left join Employee e using(employee_id)) a
where rn =1;
project_id,employee_id
from (select
project_id
,employee_id
,dense_rank() over(partition by project_id order by experience_years desc) rn
from Project p left join Employee e using(employee_id)) a
where rn =1;
way2:子查询
with aa as (
select
p.project_id
,p.employee_id
,e.name
,e.experience_years
from Project p left join Employee e using(employee_id)
)
select
aa.project_id,aa.employee_id
from aa where (aa.project_id,aa.experience_years)
in (
select
project_id,max(experience_years)
from aa
group by project_id
)
select
p.project_id
,p.employee_id
,e.name
,e.experience_years
from Project p left join Employee e using(employee_id)
)
select
aa.project_id,aa.employee_id
from aa where (aa.project_id,aa.experience_years)
in (
select
project_id,max(experience_years)
from aa
group by project_id
)
1285. 找到连续区间的开始和结束数字
way1：开窗函数差值计算
select
min(log_id) as start_id
,max(log_id) as end_id
from (select
log_id
,row_number() over(order by log_id) as rn
,(log_id - row_number() over(order by log_id)) as diff
from Logs
) a
group by a.diff order by a.log_id
min(log_id) as start_id
,max(log_id) as end_id
from (select
log_id
,row_number() over(order by log_id) as rn
,(log_id - row_number() over(order by log_id)) as diff
from Logs
) a
group by a.diff order by a.log_id
1596. 每位顾客最经常订购的商品
way1：窗口函数
select
a.customer_id,a.product_id,b.product_name
from (
select
customer_id,product_id
,rank() over(partition by customer_id order by count(1) desc) as rn
from Orders
group by customer_id,product_id
) a left join Products b using(product_id)
where rn =1 and a.product_id = b.product_id;
a.customer_id,a.product_id,b.product_name
from (
select
customer_id,product_id
,rank() over(partition by customer_id order by count(1) desc) as rn
from Orders
group by customer_id,product_id
) a left join Products b using(product_id)
where rn =1 and a.product_id = b.product_id;
way2: 多表链接
1709. 访问日期之间最大的空档期
way1：开窗函数
## 开窗函数
select
user_id,max(diff_wind) as biggest_window
from (select
user_id,visit_date
,lead(visit_date,1,'2021-01-01') over(partition by user_id order by visit_date) as next_visit_date
,datediff((lead(visit_date,1,'2021-01-01') over(partition by user_id order by visit_date)),visit_date) as diff_wind
from UserVisits) aa
group by user_id
select
user_id,max(diff_wind) as biggest_window
from (select
user_id,visit_date
,lead(visit_date,1,'2021-01-01') over(partition by user_id order by visit_date) as next_visit_date
,datediff((lead(visit_date,1,'2021-01-01') over(partition by user_id order by visit_date)),visit_date) as diff_wind
from UserVisits) aa
group by user_id
way2：自关联
1270. 向公司CEO汇报工作的所有人
way1：union all
SELECT DISTINCT employee_id FROM (
SELECT employee_id
FROM Employees WHERE manager_id = 1
UNION ALL
SELECT employee_id
FROM Employees WHERE manager_id IN (
SELECT employee_id FROM Employees WHERE manager_id = 1
)
UNION ALL
SELECT 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
)
)
) T WHERE employee_id != 1
SELECT employee_id
FROM Employees WHERE manager_id = 1
UNION ALL
SELECT employee_id
FROM Employees WHERE manager_id IN (
SELECT employee_id FROM Employees WHERE manager_id = 1
)
UNION ALL
SELECT 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
)
)
) T WHERE employee_id != 1
way2:join
SELECT 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 != 1 AND e3.manager_id = 1
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 != 1 AND e3.manager_id = 1
1412. 查找成绩处于中游的学生
way1：子查询写法
select student_id, student_name
from Student
where student_id in
(select distinct student_id from Exam)
and student_id not in
(
select student_id
from Exam where (exam_id, score) in
(select exam_id, max(score) socre
from Exam
group by exam_id)
)
and student_id not in
(
select student_id
from Exam where (exam_id, score) in
(select exam_id, min(score) socre
from Exam
group by exam_id)
)
from Student
where student_id in
(select distinct student_id from Exam)
and student_id not in
(
select student_id
from Exam where (exam_id, score) in
(select exam_id, max(score) socre
from Exam
group by exam_id)
)
and student_id not in
(
select student_id
from Exam where (exam_id, score) in
(select exam_id, min(score) socre
from Exam
group by exam_id)
)
select student_id, student_name
from Student
where (student_id, student_name) not in (
select s.student_id, s.student_name
from Student s left join Exam e on s.student_id = e.student_id
where (e.exam_id, e.score) in (
select exam_id, max(score) score
from Exam
group by exam_id
union all
select exam_id, min(score) score
from Exam
group by exam_id
) or e.exam_id is null
group by s.student_id
)
from Student
where (student_id, student_name) not in (
select s.student_id, s.student_name
from Student s left join Exam e on s.student_id = e.student_id
where (e.exam_id, e.score) in (
select exam_id, max(score) score
from Exam
group by exam_id
union all
select exam_id, min(score) score
from Exam
group by exam_id
) or e.exam_id is null
group by s.student_id
)
way2：开窗函数写法
select
a.student_id,s.student_name
from (
select
*
,if(dense_rank() over(partition by exam_id order by score desc)=1,1,0) as ma_r
,if(dense_rank() over(partition by exam_id order by score )=1,1,0) as mi_r
from Exam
) a left join Student s using(student_id)
group by a.student_id
having sum(a.ma_r) =0 and sum(a.mi_r) =0
order by a.student_id
a.student_id,s.student_name
from (
select
*
,if(dense_rank() over(partition by exam_id order by score desc)=1,1,0) as ma_r
,if(dense_rank() over(partition by exam_id order by score )=1,1,0) as mi_r
from Exam
) a left join Student s using(student_id)
group by a.student_id
having sum(a.ma_r) =0 and sum(a.mi_r) =0
order by a.student_id
way3：开窗函数写法
select
a.student_id,s.student_name
from (
select
*
,dense_rank() over(partition by exam_id order by score desc) as ma_r
,dense_rank() over(partition by exam_id order by score ) as mi_r
from Exam
) a left join Student s using(student_id)
group by a.student_id
having min(a.ma_r) <>1 and min(a.mi_r) <>1
order by a.student_id
a.student_id,s.student_name
from (
select
*
,dense_rank() over(partition by exam_id order by score desc) as ma_r
,dense_rank() over(partition by exam_id order by score ) as mi_r
from Exam
) a left join Student s using(student_id)
group by a.student_id
having min(a.ma_r) <>1 and min(a.mi_r) <>1
order by a.student_id
1767. 寻找没有被执行的任务对
way1:MYSQL RECURSIVE
with recursive table1 as (
select task_id, subtasks_count subtask_id from Tasks
union all
select task_id, subtask_id-1 from table1 where subtask_id > 1
)
select
task_id,
subtask_id
from table1
left join Executed E using(task_id, subtask_id)
where E.task_id is null
select task_id, subtasks_count subtask_id from Tasks
union all
select task_id, subtask_id-1 from table1 where subtask_id > 1
)
select
task_id,
subtask_id
from table1
left join Executed E using(task_id, subtask_id)
where E.task_id is null
way2:
WITH recursive t(n) as --创建递归序列
(
SELECT 1
UNION
SELECT n+1 FROM t WHERE n<=19
)
SELECT task_id,n AS subtask_id
FROM Tasks,t
WHERE n<=subtasks_count --筛选出每个task_id可能的结果
AND (task_id,n) not in (SELECT * FROM Executed ) --剔除每个task_id已经有的结果
ORDER BY task_id
(
SELECT 1
UNION
SELECT n+1 FROM t WHERE n<=19
)
SELECT task_id,n AS subtask_id
FROM Tasks,t
WHERE n<=subtasks_count --筛选出每个task_id可能的结果
AND (task_id,n) not in (SELECT * FROM Executed ) --剔除每个task_id已经有的结果
ORDER BY task_id
with recursive t as(
select 1 as n
union
select n+1 from t where n<=19
)
select task_id,n as subtask_id
from tasks,t
where n<=subtasks_count
and (task_id,n) not in (
select *
from executed
)
select 1 as n
union
select n+1 from t where n<=19
)
select task_id,n as subtask_id
from tasks,t
where n<=subtasks_count
and (task_id,n) not in (
select *
from executed
)
1225. 报告系统状态的连续日期
way1：
select type as period_state, min(date) as start_date, max(date) as end_date
from
(
select type, date, subdate(date,row_number()over(partition by type order by date)) as diff
from
(
select 'failed' as type, fail_date as date from Failed
union all
select 'succeeded' as type, success_date as date from Succeeded
) a
)a
where date between '2019-01-01' and '2019-12-31'
group by type,diff
order by start_date
from
(
select type, date, subdate(date,row_number()over(partition by type order by date)) as diff
from
(
select 'failed' as type, fail_date as date from Failed
union all
select 'succeeded' as type, success_date as date from Succeeded
) a
)a
where date between '2019-01-01' and '2019-12-31'
group by type,diff
order by start_date
way2
# Write your MySQL query statement below
# 开窗函数 典型写法 2
select
aa.type as period_state
,min(dt) as start_date
,max(dt) as end_date
from (
select
a.*
,row_number() over(partition by a.type order by dt) as an
,row_number() over(order by dt) as rn
from (select
'failed' as type
,fail_date as dt
from Failed
union all
select
'succeeded' as type
,success_date as dt
from Succeeded) a
) aa
where year(aa.dt) = '2019'
group by aa.type,rn-an
order by start_date
# 开窗函数 典型写法 2
select
aa.type as period_state
,min(dt) as start_date
,max(dt) as end_date
from (
select
a.*
,row_number() over(partition by a.type order by dt) as an
,row_number() over(order by dt) as rn
from (select
'failed' as type
,fail_date as dt
from Failed
union all
select
'succeeded' as type
,success_date as dt
from Succeeded) a
) aa
where year(aa.dt) = '2019'
group by aa.type,rn-an
order by start_date
题型
简单：26道
中等：21道
困难：3道
0 条评论
下一页
