# 力扣MySQL题解
# 目录
[TOC]
# 力扣-SQL入门
# 1.选择
# 595. 大的国家 (opens new window)
# Write your MySQL query statement below
select name , population, area
from World
where area>=3000000 or population>=25000000
1
2
3
4
2
3
4
# 1757. 可回收且低脂的产品 (opens new window)
# Write your MySQL query statement below
select product_id
from Products
where 'Y'=recyclable and 'Y'=low_fats
1
2
3
4
2
3
4
# 584. 寻找用户推荐人 (opens new window)
- 易错,本题,必须判断
referee_id is null
# Write your MySQL query statement below
select name
from customer
where ( not referee_id=2 ) or referee_id is null
1
2
3
4
2
3
4
# 183. 从不订购的客户 (opens new window)⭐️
- not in的使用
# Write your MySQL query statement below
select name as Customers
from Customers
where Id not in
(
select CustomerId
from Orders
)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
- 更加明确的写法如下⭐️⭐️
select Customers.name as Customers
from Customers
where Customers.id not in
(
select Customerid from Orders
)
1
2
3
4
5
6
7
2
3
4
5
6
7
# 2.排序 & 修改
# 1873. 计算特殊奖金 (opens new window)⭐️
- 学习case end的使用
# Write your MySQL query statement below
select employee_id ,
case
when ( left(name,1)!='M' and employee_id % 2=1 ) then salary
else 0
end
as bonus
from Employees
order by employee_id
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
# 627. 变更性别 (opens new window)⭐️
- if语句
# Write your MySQL query statement below
update Salary
set sex=
if( 'f'=sex ,
'm','f')
1
2
3
4
5
2
3
4
5
- case解法
# Write your MySQL query statement below
update Salary
set sex=
case sex
when 'f' then 'm'
when 'm' then 'f'
end
1
2
3
4
5
6
7
2
3
4
5
6
7
# 196. 删除重复的电子邮箱 (opens new window)⭐️
- 自连接!学习⭐️⭐️
# Please write a DELETE statement and DO NOT write a SELECT statement.
# Write your MySQL query statement below
delete p1
from Person p1 ,Person p2
where p1.Email =p2.Email and p1.Id > p2.Id
1
2
3
4
5
2
3
4
5
# 3.字符串处理函数/正则
# 1667. 修复表中的名字 (opens new window)
# Write your MySQL query statement below
SELECT user_id,
CONCAT(UPPER(SUBSTRING(name, 1, 1)),
LOWER(SUBSTRING(name, 2, (SELECT LENGTH(name))))) name
FROM Users
ORDER BY user_id
1
2
3
4
5
6
7
2
3
4
5
6
7
# 1484. 按日期分组销售产品 (opens new window)
- 记忆,不会的
GROUP_CONCAT()
函数
select
sell_date,
# 获取“不同的”产品数【count(distinct product)】
count(distinct product) as num_sold,
# “不同的”【distinct product】产品按照字典排序【order by product】 & “,”分割【separator ','】
group_concat(distinct product order by product separator ',') as products
from Activities
group by sell_date
order by sell_date;
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
# 1527. 患某种疾病的患者 (opens new window)
# Write your MySQL query statement below
select patient_id,patient_name, conditions
from Patients
where conditions like '% DIAB1%'
or conditions like'DIAB1%'
1
2
3
4
5
2
3
4
5
# 4.组合查询&指定选取
# 1965. 丢失信息的雇员 (opens new window)
# Write your MySQL query statement below
select employee_id from (
select e.employee_id employee_id from Employees e left join Salaries s on e.employee_id=s.employee_id where salary is null
union all
select s.employee_id employee_id from Employees e right join Salaries s on e.employee_id=s.employee_id where name is null
) x order by employee_id;
1
2
3
4
5
6
7
2
3
4
5
6
7
# 1795. 每个产品在不同商店的价格 (opens new window)
# Write your MySQL query statement below
select product_id, 'store1' store, store1 price from products where store1 is not NULL
UNION
select product_id, 'store2' store, store2 price from products where store2 is not NULL
UNION
select product_id, 'store3' store, store3 price from products where store3 is not NULL
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# 608. 树节点 (opens new window)
# Write your MySQL query statement below
# Write your MySQL query statement below
select id,(
case WHEN p_id is null THEN 'Root'
WHEN id in(select p_id from tree) THEN 'Inner'
else 'Leaf' END)
as type from tree
order by id
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# 176. 第二高的薪水 (opens new window)
# Write your MySQL query statement below
SELECT
(SELECT DISTINCT
Salary
FROM
Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1) AS SecondHighestSalary
;
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
# 5.合并
多表的联结又分为以下几种类型:
1)左联结(left join),联结结果保留左表的全部数据
2)右联结(right join),联结结果保留右表的全部数据
3)内联结(inner join),取两表的公共数据
# 175. 组合两个表 (opens new window)
# Write your MySQL query statement below
select FirstName, LastName, City, State
from Person left join Address
on Person.PersonId = Address.PersonId
1
2
3
4
2
3
4
# 1581. 进店却未进行过交易的顾客 (opens new window)
# Write your MySQL query statement below
select a.customer_id, count(1) count_no_trans
from Visits a
where a.visit_id not in (select b.visit_id from Transactions b)
group by a.customer_id
1
2
3
4
5
6
2
3
4
5
6
# 1148. 文章浏览 I (opens new window)
# Write your MySQL query statement below
select distinct author_id as id
from Views
where author_id = viewer_id
order by author_id
1
2
3
4
5
6
2
3
4
5
6
# 6.合并
# 197. 上升的温度 (opens new window)
# Write your MySQL query statement below
SELECT b.Id
FROM Weather as a,Weather as b
WHERE a.Temperature < b.Temperature and DATEDIFF(a.RecordDate,b.RecordDate) = -1;
1
2
3
4
2
3
4
# 607. 销售员 (opens new window)
select distinct name from SalesPerson where sales_id not in (
select distinct sales_id from Orders where com_id=(
select com_id from Company where name="RED"
)
);
1
2
3
4
5
2
3
4
5
# 7.计算函数
# 1141. 查询近30天活跃用户数 (opens new window)
# Write your MySQL query statement below
# Write your MySQL query statement below
select activity_date as day,count(distinct user_id) as active_users
from
Activity
where
activity_date between '2019-06-28' and '2019-07-27'
group by
activity_date;
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
# 1693. 每天的领导和合伙人 (opens new window)
# Write your MySQL query statement below
select date_id , make_name ,count(distinct lead_id) unique_leads , count(distinct partner_id) unique_partners
from dailysales
group by date_id,make_name;
1
2
3
4
5
2
3
4
5
# 1729. 求关注者的数量 (opens new window)
# Write your MySQL query statement below
select user_id, count(1) followers_count
from Followers
group by user_id
order by user_id;
1
2
3
4
5
2
3
4
5
# 8.计算函数
# 511. 游戏玩法分析 I (opens new window)
# Write your MySQL query statement below
select player_id,min(event_date) as 'first_login'
from Activity
group by player_id
1
2
3
4
2
3
4
# 586. 订单最多的客户 (opens new window)
SELECT
customer_number
FROM
orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC
LIMIT 1
1
2
3
4
5
6
7
2
3
4
5
6
7
# 1890. 2020年最后一次登录 (opens new window)
# Write your MySQL query statement below
# Write your MySQL query statement below
select
user_id,max(time_stamp) as last_stamp
from
Logins
where
time_stamp between '2020-01-01' and '2021-01-01'
group by
user_id
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
# 1741. 查找每个员工花费的总时间 (opens new window)
SELECT
event_day day, emp_id, SUM(out_time - in_time) total_time
FROM
Employees
GROUP BY
event_day, emp_id;
1
2
3
4
5
6
7
2
3
4
5
6
7
# 9.控制流
# 1393. 股票的资本损益 (opens new window)
# Write your MySQL query statement below
# Write your MySQL query statement below
# 方法一 case when
# select stock_name, sum(
# case when operation = 'buy' then -price
# else price end
# ) as capital_gain_loss
# from stocks
# group by stock_name;
# 方法二 if语句
select stock_name, sum(
if(operation = 'buy', -price, price)
) as capital_gain_loss
from stocks
group by stock_name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 1407. 排名靠前的旅行者 (opens new window)
# Write your MySQL query statement below
# Write your MySQL query statement below
# 1.先查出每个有行程的用户的距离 用到group by 和sum
# 2.由于没有行程的用户也需要查找所以需用到left join(主表数据不受影响),
# 再通过IFNULL()函数对行程为null的用户赋值为0,最后对其order by排序就行了
select name, ifnull (travelled_distance,0) as travelled_distance
from Users
left join
(
select
user_id,sum(distance) as travelled_distance
from Rides
group by user_id
)t1
on t1.user_id= Users.id
order by travelled_distance desc, name asc
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 1158. 市场分析 I (opens new window)
# Write your MySQL query statement below
select Users.user_id as buyer_id,
join_date,
ifnull(UserBuy.cnt, 0) as orders_in_2019
from Users
left join (
select buyer_id, count(order_id) cnt
from Orders
where order_date between '2019-01-01' and '2019-12-31'
group by buyer_id
) UserBuy
on Users.user_id = UserBuy.buyer_id
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
# 10.过滤
# 182. 查找重复的电子邮箱 (opens new window)
# Write your MySQL query statement below
select Email
from Person
group by Email
having count(Email) > 1
1
2
3
4
5
2
3
4
5
# 1050. 合作过至少三次的演员和导演 (opens new window)
# Write your MySQL query statement below
select actor_id , director_id
from ActorDirector
group by actor_id , director_id
having count(timestamp) >= 3
1
2
3
4
5
2
3
4
5
# 1587. 银行账户概要 II (opens new window)
# Write your MySQL query statement below
select name, SUM(amount) balance
from
Users u
join
Transactions t
on
u.account = T.account
group by
u.account
having
sum(amount) > 10000;
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
# 1084. 销售分析III (opens new window)
select product_id, product_name
from product
where product_id in (
select
product_id
from sales
group by product_id
having max(sale_date) <= '2019-03-31' and min(sale_date) >= '2019-01-01'
)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
# 力扣-SQL 基础plus会员-11天
# 1.数值处理函数
# 1699. 两人之间的通话次数 (opens new window)
# Write your MySQL query statement below
# Write your MySQL query statement below
SELECT
person1,person2,
count(*) call_count,
sum(duration) total_duration
FROM (
SELECT
IF(from_id>to_id, to_id, from_id) person1,
IF(from_id>to_id,from_id,to_id) person2,
duration
FROM calls
) c
GROUP BY
person1, person2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 1251. 平均售价 (opens new window)
SELECT u.product_id product_id,
ROUND(SUM(u.units*p.price)/SUM(u.units),2) average_price
FROM UnitsSold u ,Prices p
WHERE u.product_id=p.product_id
AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY u.product_id
1
2
3
4
5
6
7
2
3
4
5
6
7
# 1571. 仓库经理 (opens new window)
# Write your MySQL query statement below
SELECT
w.name WAREHOUSE_NAME,
SUM(p.Width * p.Length * p.Height * w.units) VOLUME
FROM
warehouse w
LEFT JOIN
products p
ON
w.product_id = p.product_id
GROUP BY w.name;
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
# 1445. 苹果和桔子 (opens new window)
select sale_date,
sum(
if(fruit = 'apples', sold_num, -sold_num)
) as diff
from sales
group by sale_date
1
2
3
4
5
6
2
3
4
5
6
# 2.数值处理函数
# 1193. 每月交易 I (opens new window)
select date_format(trans_date,'%Y-%m') as month,
country, count(*) as trans_count,
sum(case state when 'approved' then 1 else 0 end) as approved_count,
sum(amount) as trans_total_amount,
sum(case state when 'approved' then amount else 0 end) as approved_total_amount
from Transactions
group by month,country;
1
2
3
4
5
6
7
2
3
4
5
6
7
# 1633. 各赛事的用户注册率 (opens new window)
# Write your MySQL query statement below
select contest_id,
round(count(user_id) / (select count(1) from Users) * 100, 2) as percentage
from Register
group by contest_id
order by percentage desc , contest_id
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# 1173. 即时食物配送 I (opens new window)
# Write your MySQL query statement below
select round (
sum(order_date = customer_pref_delivery_date) /
count(*) * 100,
2
) as immediate_percentage
from Delivery
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# 1211. 查询结果的质量和占比 (opens new window)
# Write your MySQL query statement below
select
query_name,
round(avg(rating/position), 2) quality,
round(sum(if(rating < 3, 1, 0)) * 100 / count(*), 2) poor_query_percentage
from queries
group by query_name
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# 3.连接
# 1607. 没有卖出的卖家 (opens new window)
/* write your pl/sql query statement below */
select s.seller_name from seller s where s.seller_id not in (
select o.seller_id from orders o where o.sale_date like '2020%'
) order by seller_name
1
2
3
4
5
2
3
4
5
# 619. 只出现一次的最大数字 (opens new window)
select
(
select ifnull(num, null) as num from mynumbers
group by num
having count(*) = 1
order by num desc
limit 0, 1
) num
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
# 1112. 每位学生的最高成绩 (opens new window)
SELECT student_id, min(course_id) AS course_id, grade
FROM enrollments
WHERE (student_id, grade) IN
(
SELECT student_id, MAX(grade) AS grade
FROM Enrollments
GROUP BY student_id
)
GROUP BY student_id, grade
ORDER BY student_id
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
# 1398. 购买了产品 A 和产品 B 却没有购买产品 C 的顾客 (opens new window)
select o.customer_id, c.customer_name from orders o
left join customers c
on o.customer_id = c.customer_id
group by customer_id
having
SUM(if(product_name = 'A', 1, 0)) > 0 and
SUM(if(product_name = 'B', 1, 0)) > 0 and
SUM(if(product_name = 'C', 1, 0)) = 0
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# 4.连接
# 1440. 计算布尔表达式的值 (opens new window)
select e.left_operand,e.operator,e.right_operand,
case e.operator
when '>' then if(v1.value>v2.value,'true','false')
when '<' then if(v1.value<v2.value,'true','false')
else if(v1.value=v2.value,'true','false')
end value
from Expressions e
left join Variables v1 on v1.name = e.left_operand
left join Variables v2 on v2.name = e.right_operand
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
# 1264. 页面推荐 (opens new window)
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
)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
# 570. 至少有5名直接下属的经理 (opens new window)
select
name
from
employee as t1 join
(select
managerid
from
employee
group by managerid
having count(managerid) >= 5) as t2
on t1.id = t2.managerid
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
# 1303. 求团队人数 (opens new window)
# write your mysql query statement below
select e.employee_id,t1.team_size from employee e inner join
(
select team_id,
count(team_id) as team_size from employee group by team_id
) t1
on t1.team_id = e.team_id
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# 5.连接
# 1280. 学生们参加各科测试的次数 (opens new window)
# 自连接
with cte1 as
(
select *
from students,subjects
)
select
a.student_id,
a.student_name,
a.subject_name,
ifnull(count(b.subject_name),0) as attended_exams
from cte1 a
left join Examinations b
using(student_id,subject_name)
group by 1,2,3
order by 1,2,3
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 1501. 可以放心投资的国家 (opens new window)
# write your mysql query statement below
select c.name as country
from calls, person, country c
where (caller_id = id or callee_id = id) and country_code = left(phone_number, 3)
group by country_code
having avg(duration) > (select avg(duration) from calls);
1
2
3
4
5
6
2
3
4
5
6
# 184. 部门工资最高的员工 (opens new window)
select
department.name as 'department',
employee.name as 'employee',
salary
from
employee
join
department on employee.departmentid = department.id
where
(employee.departmentid , salary) in
( select
departmentid, max(salary)
from
employee
group by departmentid
)
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 580. 统计各专业学生人数 (opens new window)
select
dept_name, count(student_id) as student_number
from
department
left outer join
student on department.dept_id = student.dept_id
group by department.dept_name
order by student_number desc , department.dept_name
;
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
# 6.连接
# 1294. 不同国家的天气类型 (opens new window)
# Write your MySQL query statement below
SELECT country_name AS 'country_name',
CASE
WHEN AVG(w1.weather_state) <= 15 THEN 'Cold'
WHEN AVG(w1.weather_state) >= 25 THEN 'Hot'
ELSE 'Warm'
END
AS 'weather_type'
FROM
Countries AS c1
INNER JOIN Weather AS w1
ON c1.country_id = w1.country_id
WHERE w1.day BETWEEN '2019-11-01' AND '2019-11-30'
GROUP BY c1.country_id
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 626. 换座位 (opens new window)
select
(case
when mod(id, 2) != 0 and counts != id then id + 1
when mod(id, 2) != 0 and counts = id then id
else id - 1
end) as id,
student
from
seat,
(select
count(*) as counts
from
seat) as seat_counts
order by id asc;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 1783. 大满贯数量 (opens new window)
# Write your MySQL query statement below
#计算Championships表中每个运动员id出现的次数
SELECT player_id,player_name,
SUM(IF(player_id=Wimbledon,1,0)+IF(player_id=Fr_open,1,0)+IF(player_id=US_open,1,0)+IF(player_id=Au_open,1,0)) AS grand_slams_count
FROM Championships
JOIN Players
GROUP BY player_id
HAVING grand_slams_count>0
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
# 1164. 指定日期的产品价格 (opens new window)
select p1.product_id, ifnull(p2.new_price, 10) as price
from (
select distinct product_id
from products
) as p1 -- 所有的产品
left join (
select product_id, new_price
from products
where (product_id, change_date) in (
select product_id, max(change_date)
from products
where change_date <= '2019-08-16'
group by product_id
)
) as p2 -- 在 2019-08-16 之前有过修改的产品和最新的价格
on p1.product_id = p2.product_id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 7.连接
# 603. 连续空余座位 (opens new window)
# Write your MySQL query statement below
select distinct a.seat_id
from cinema a join cinema b
on abs(a.seat_id - b.seat_id) = 1
and a.free = true and b.free = true
order by a.seat_id
;
1
2
3
4
5
6
7
2
3
4
5
6
7
# 1731. 每位经理的下属员工数量 (opens new window)
# Write your MySQL query statement below
select
e1.employee_id,
e1.name,
count(*) as reports_count,
ROUND(avg(e2.age), 0) as average_age
from employees e1
join employees e2
on e2.reports_to = e1.employee_id
group by employee_id
order by employee_id
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
# 1747. 应该被禁止的 Leetflex 账户 (opens new window)
# Write your MySQL query statement below
select distinct l1.account_id from loginfo l1
join loginfo l2
on
l1.account_id = l2.account_id and
l1.ip_address != l2.ip_address and
( l1.login >= l2.login and l1.login <= l2.logout )
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# 181. 超过经理收入的员工 (opens new window)
select
a.name as employee
from employee as a join employee as b
on a.managerid = b.id
and a.salary > b.salary
;
1
2
3
4
5
6
2
3
4
5
6
# 8.不等式连接
# 1459. 矩形面积 (opens new window)
# Write your MySQL query statement below
SELECT
p1.id P1,
p2.id P2,
ABS(p1.x_value-p2.x_value)*ABS(p1.y_value-p2.y_value) AREA
FROM points p1,points p2
WHERE p1.id<p2.id
AND p1.x_value!=p2.x_value
AND p1.y_value!=p2.y_value
ORDER BY AREA desc,p1.id,p2.id
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
# 180. 连续出现的数字 (opens new window)
SELECT DISTINCT
l1.Num AS ConsecutiveNums
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num
;
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
# 1988. 找出每所学校的最低分数要求 (opens new window)
# Write your MySQL query statement below
SELECT
school_id,
IFNULL(min(score), -1) as score
FROM (
SELECT
s.school_id,
e.score
FROM schools s
LEFT JOIN exam e ON s.capacity >= e.student_count
) t
GROUP BY school_id
ORDER BY school_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
# 9.子查询
# 1549. 每件商品的最新订单 (opens new window)
select p.product_name, o.product_id, o.order_id, o.order_date from orders o
left join products p
on p.product_id = o.product_id
where (o.product_id, order_date) in
(
select product_id, max(order_date) from orders
group by product_id
)
order by product_name asc, product_id asc, order_id asc
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
# 1321. 餐馆营业额变化增长 (opens new window)
select
distinct a.visited_on,sum(b.amount) as amount,round(sum(b.amount)/7,2) as average_amount
from Customer a
join Customer b
on datediff(a.visited_on,b.visited_on) <= 6 and datediff(a.visited_on,b.visited_on) >= 0
group by a.visited_on,a.customer_id
having count(distinct b.visited_on) = 7
order by visited_on
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# 1045. 买下所有产品的客户 (opens new window)
select customer_id from customer
group by customer_id
having count(distinct product_key) = (select count(distinct product_key) from product)
1
2
3
4
2
3
4
# 10.子查询
# 1341. 电影评分 (opens new window)
select T.results FROM
(
SELECT U.name AS results,MR.user_id
FROM Users U,MovieRating MR
WHERE U.user_id = MR.user_id
GROUP BY MR.user_id,U.name
ORDER BY COUNT(1) DESC,U.NAME ASC
limit 1
) T
union all
select Q.results FROM
(
SELECT M.title AS results
FROM Movies M,MovieRating MM
WHERE M.movie_id = MM.movie_id
AND MM.created_at LIKE '2020-02%'
GROUP BY M.title
ORDER BY AVG(MM.rating) DESC,M.title ASC
limit 1
) Q
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 1867. 最大数量高于平均水平的订单 (opens new window)
select
order_id
from (
-- 每个订单的最大数量 > max(每个订单的平均数量)
select
*,
max(quantity) > max(avg(quantity)) over() as flag
from OrdersDetails
group by order_id
) as base
where flag = 1
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
# 11.子查询
# 550. 游戏玩法分析 IV (opens new window)
# Write your MySQL query statement below
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)=1
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
# 262. 行程和用户 (opens new window)
select request_at 'Day',
round(count(if(status!='completed',status,null))/count(*),2) 'Cancellation Rate'
from Trips
where request_at between '2013-10-01' and '2013-10-03'
and client_id not in (select users_id from Users where banned='Yes')
and driver_id not in (select users_id from Users where banned='Yes')
group by request_at;
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8