我要评论高级查询和连接
1341.电影评分
表：movies
+---------------+---------+
| column name | type |
+---------------+---------+
| movie_id | int |
| title | varchar |
+---------------+---------+
movie_id 是这个表的主键(具有唯一值的列)。
title 是电影的名字。
表：users
+---------------+---------+
| column name | type |
+---------------+---------+
| user_id | int |
| name | varchar |
+---------------+---------+
user_id 是表的主键(具有唯一值的列)。
表：movierating
+---------------+---------+
| column name | type |
+---------------+---------+
| movie_id | int |
| user_id | int |
| rating | int |
| created_at | date |
+---------------+---------+
(movie_id, user_id) 是这个表的主键(具有唯一值的列的组合)。
这个表包含用户在其评论中对电影的评分 rating 。
created_at 是用户的点评日期。
请你编写一个解决方案：
- 查找评论电影数量最多的用户名。如果出现平局，返回字典序较小的用户名。
- 查找在
february 2020平均评分最高 的电影名称。如果出现平局，返回字典序较小的电影名称。
字典序 ，即按字母在字典中出现顺序对字符串排序，字典序较小则意味着排序靠前。
返回结果格式如下例所示。
示例 1：
输入：
movies 表：
+-------------+--------------+
| movie_id | title |
+-------------+--------------+
| 1 | avengers |
| 2 | frozen 2 |
| 3 | joker |
+-------------+--------------+
users 表：
+-------------+--------------+
| user_id | name |
+-------------+--------------+
| 1 | daniel |
| 2 | monica |
| 3 | maria |
| 4 | james |
+-------------+--------------+
movierating 表：
+-------------+--------------+--------------+-------------+
| movie_id | user_id | rating | created_at |
+-------------+--------------+--------------+-------------+
| 1 | 1 | 3 | 2020-01-12 |
| 1 | 2 | 4 | 2020-02-11 |
| 1 | 3 | 2 | 2020-02-12 |
| 1 | 4 | 1 | 2020-01-01 |
| 2 | 1 | 5 | 2020-02-17 |
| 2 | 2 | 2 | 2020-02-01 |
| 2 | 3 | 2 | 2020-03-01 |
| 3 | 1 | 3 | 2020-02-22 |
| 3 | 2 | 4 | 2020-02-25 |
+-------------+--------------+--------------+-------------+
输出：
result 表：
+--------------+
| results |
+--------------+
| daniel |
| frozen 2 |
+--------------+
解释：
daniel 和 monica 都点评了 3 部电影（"avengers", "frozen 2" 和 "joker"） 但是 daniel 字典序比较小。
frozen 2 和 joker 在 2 月的评分都是 3.5，但是 frozen 2 的字典序比较小。
解答：
# write your mysql query statement below
(
select
u.name as results
from
movierating m
left join
users u
on
m.user_id = u.user_id
group by
u.user_id
order by
count(m.movie_id) desc,name
limit 1
)
union all
(
select
m1.title as results
from
movierating m
left join
movies m1
on
m.movie_id = m1.movie_id
where
date_format(created_at,"%y-%m") = "2020-02"
group by
m.movie_id
order by
avg(rating) desc,results
limit 1
)
1321.餐馆营业额变化增长
表: customer
+---------------+---------+
| column name | type |
+---------------+---------+
| customer_id | int |
| name | varchar |
| visited_on | date |
| amount | int |
+---------------+---------+
在 sql 中，(customer_id, visited_on) 是该表的主键。
该表包含一家餐馆的顾客交易数据。
visited_on 表示 (customer_id) 的顾客在 visited_on 那天访问了餐馆。
amount 是一个顾客某一天的消费总额。
你是餐馆的老板，现在你想分析一下可能的营业额变化增长（每天至少有一位顾客）。
计算以 7 天（某日期 + 该日期前的 6 天）为一个时间段的顾客消费平均值。average_amount 要 保留两位小数。
结果按 visited_on 升序排序。
返回结果格式的例子如下。
示例 1:
输入：
customer 表:
+-------------+--------------+--------------+-------------+
| customer_id | name | visited_on | amount |
+-------------+--------------+--------------+-------------+
| 1 | jhon | 2019-01-01 | 100 |
| 2 | daniel | 2019-01-02 | 110 |
| 3 | jade | 2019-01-03 | 120 |
| 4 | khaled | 2019-01-04 | 130 |
| 5 | winston | 2019-01-05 | 110 |
| 6 | elvis | 2019-01-06 | 140 |
| 7 | anna | 2019-01-07 | 150 |
| 8 | maria | 2019-01-08 | 80 |
| 9 | jaze | 2019-01-09 | 110 |
| 1 | jhon | 2019-01-10 | 130 |
| 3 | jade | 2019-01-10 | 150 |
+-------------+--------------+--------------+-------------+
输出：
+--------------+--------------+----------------+
| visited_on | amount | average_amount |
+--------------+--------------+----------------+
| 2019-01-07 | 860 | 122.86 |
| 2019-01-08 | 840 | 120 |
| 2019-01-09 | 840 | 120 |
| 2019-01-10 | 1000 | 142.86 |
+--------------+--------------+----------------+
解释：
第一个七天消费平均值从 2019-01-01 到 2019-01-07 是restaurant-growth/restaurant-growth/ (100 + 110 + 120 + 130 + 110 + 140 + 150)/7 = 122.86
第二个七天消费平均值从 2019-01-02 到 2019-01-08 是 (110 + 120 + 130 + 110 + 140 + 150 + 80)/7 = 120
第三个七天消费平均值从 2019-01-03 到 2019-01-09 是 (120 + 130 + 110 + 140 + 150 + 80 + 110)/7 = 120
第四个七天消费平均值从 2019-01-04 到 2019-01-10 是 (130 + 110 + 140 + 150 + 80 + 110 + 130 + 150)/7 = 142.86
解答：
select
c1.visited_on ,
c1.amount,
round(c1.amount/7,2) as average_amount
from
(
select
visited_on,
(
select
sum(amount)
from
customer ct2
where
ct2.visited_on
between
adddate(ct1.visited_on,-6) and ct1.visited_on
) as amount
from
customer ct1
group by
visited_on
) as c1
left join
(
select
distinct visited_on
from
customer
where
visited_on >= ( select adddate(min(visited_on),6) from customer )
) as c2
on
c1.visited_on = c2.visited_on
where
c2.visited_on is not null
602.好友申请ii：谁有最多的好友
requestaccepted 表：
+----------------+---------+
| column name | type |
+----------------+---------+
| requester_id | int |
| accepter_id | int |
| accept_date | date |
+----------------+---------+
(requester_id, accepter_id) 是这张表的主键(具有唯一值的列的组合)。
这张表包含发送好友请求的人的 id ，接收好友请求的人的 id ，以及好友请求通过的日期。
编写解决方案，找出拥有最多的好友的人和他拥有的好友数目。
生成的测试用例保证拥有最多好友数目的只有 1 个人。
查询结果格式如下例所示。
示例 1：
输入：
requestaccepted 表：
+--------------+-------------+-------------+
| requester_id | accepter_id | accept_date |
+--------------+-------------+-------------+
| 1 | 2 | 2016/06/03 |
| 1 | 3 | 2016/06/08 |
| 2 | 3 | 2016/06/08 |
| 3 | 4 | 2016/06/09 |
+--------------+-------------+-------------+
输出：
+----+-----+
| id | num |
+----+-----+
| 3 | 3 |
+----+-----+
解释：
编号为 3 的人是编号为 1 ，2 和 4 的人的好友，所以他总共有 3 个好友，比其他人都多。
**进阶：**在真实世界里，可能会有多个人拥有好友数相同且最多，你能找到所有这些人吗？
select
id,
sum(num) as num
from
(
(
select
requester_id as id,
count(1) as num
from
requestaccepted
group by
requester_id
)
union all
(
select
accepter_id as id,
count(1) as num
from
requestaccepted
group by
accepter_id
)
) as ra
group by
id
order by
num
desc
limit 1
285.2016年的投资
insurance 表：
+-------------+-------+
| column name | type |
+-------------+-------+
| pid | int |
| tiv_2015 | float |
| tiv_2016 | float |
| lat | float |
| lon | float |
+-------------+-------+
pid 是这张表的主键(具有唯一值的列)。
表中的每一行都包含一条保险信息，其中：
pid 是投保人的投保编号。
tiv_2015 是该投保人在 2015 年的总投保金额，tiv_2016 是该投保人在 2016 年的总投保金额。
lat 是投保人所在城市的纬度。题目数据确保 lat 不为空。
lon 是投保人所在城市的经度。题目数据确保 lon 不为空。
编写解决方案报告 2016 年 (tiv_2016) 所有满足下述条件的投保人的投保金额之和：
- 他在 2015 年的投保额 (
tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同。 - 他所在的城市必须与其他投保人都不同（也就是说 (
lat, lon) 不能跟其他任何一个投保人完全相同）。
tiv_2016 四舍五入的 两位小数 。
查询结果格式如下例所示。
示例 1：
输入：
insurance 表：
+-----+----------+----------+-----+-----+
| pid | tiv_2015 | tiv_2016 | lat | lon |
+-----+----------+----------+-----+-----+
| 1 | 10 | 5 | 10 | 10 |
| 2 | 20 | 20 | 20 | 20 |
| 3 | 10 | 30 | 20 | 20 |
| 4 | 10 | 40 | 40 | 40 |
+-----+----------+----------+-----+-----+
输出：
+----------+
| tiv_2016 |
+----------+
| 45.00 |
+----------+
解释：
表中的第一条记录和最后一条记录都满足两个条件。
tiv_2015 值为 10 与第三条和第四条记录相同，且其位置是唯一的。
第二条记录不符合任何一个条件。其 tiv_2015 与其他投保人不同，并且位置与第三条记录相同，这也导致了第三条记录不符合题目要求。
因此，结果是第一条记录和最后一条记录的 tiv_2016 之和，即 45 。
解答：
select
round(sum(tiv_2016),2) as tiv_2016
from
(
select
tiv_2016,
(
select
count(1)
from
insurance is1
where
concat(is1.lat,is1.lon) = concat(i1.lat,i1.lon)
and
is1.pid != i1.pid
) as lat_lon,
(
select
count(1)
from
insurance is1
where
is1.tiv_2015 = i1.tiv_2015
and
is1.pid != i1.pid
) as tiv_2015
from
insurance i1
) as tmp
where
lat_lon = 0
and
tiv_2015 > 0
select
round(sum(tiv_2016),2) as tiv_2016
from
insurance
where
tiv_2015
in(
select
tiv_2015
from
insurance
group by
tiv_2015
having
count(*)>1
)
and
(lat, lon)
in(
select
lat, lon
from
insurance
group by
lat, lon
having
count(*)=1
);
185.部门工资前三高的所有员工
表: employee
+--------------+---------+
| column name | type |
+--------------+---------+
| id | int |
| name | varchar |
| salary | int |
| departmentid | int |
+--------------+---------+
id 是该表的主键列(具有唯一值的列)。
departmentid 是 department 表中 id 的外键（reference 列）。
该表的每一行都表示员工的id、姓名和工资。它还包含了他们部门的id。
表: department
+-------------+---------+
| column name | type |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
id 是该表的主键列(具有唯一值的列)。
该表的每一行表示部门id和部门名。
公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。
编写解决方案，找出每个部门中 收入高的员工 。
以 任意顺序 返回结果表。
返回结果格式如下所示。
示例 1:
输入:
employee 表:
+----+-------+--------+--------------+
| id | name | salary | departmentid |
+----+-------+--------+--------------+
| 1 | joe | 85000 | 1 |
| 2 | henry | 80000 | 2 |
| 3 | sam | 60000 | 2 |
| 4 | max | 90000 | 1 |
| 5 | janet | 69000 | 1 |
| 6 | randy | 85000 | 1 |
| 7 | will | 70000 | 1 |
+----+-------+--------+--------------+
department 表:
+----+-------+
| id | name |
+----+-------+
| 1 | it |
| 2 | sales |
+----+-------+
输出:
+------------+----------+--------+
| department | employee | salary |
+------------+----------+--------+
| it | max | 90000 |
| it | joe | 85000 |
| it | randy | 85000 |
| it | will | 70000 |
| sales | henry | 80000 |
| sales | sam | 60000 |
+------------+----------+--------+
解释:
在it部门:
- max的工资最高
- 兰迪和乔都赚取第二高的独特的薪水
- 威尔的薪水是第三高的
在销售部:
- 亨利的工资最高
- 山姆的薪水第二高
- 没有第三高的工资，因为只有两名员工
解答：
select
d.name as department,
e.name as employee,
e.salary as salary
from
department d
left join
employee e
on
e.departmentid = d.id
where
e.salary in
(
# 嵌套一层查询，in不能直接与limit使用
select
*
from
(
# 先查询对应部门前三的薪资
select
salary
from
employee emp1
where
emp1.departmentid = e.departmentid
group by
emp1.salary
order by
emp1.salary desc
limit 3
) as tmp
)
高级字符串函数/正则表达式/子句
1667.修复表中的名字
表： users
+----------------+---------+
| column name | type |
+----------------+---------+
| user_id | int |
| name | varchar |
+----------------+---------+
user_id 是该表的主键(具有唯一值的列)。
该表包含用户的 id 和名字。名字仅由小写和大写字符组成。
编写解决方案，修复名字，使得只有第一个字符是大写的，其余都是小写的。
返回按 user_id 排序的结果表。
返回结果格式示例如下。
示例 1：
输入：
users table:
+---------+-------+
| user_id | name |
+---------+-------+
| 1 | alice |
| 2 | bob |
+---------+-------+
输出：
+---------+-------+
| user_id | name |
+---------+-------+
| 1 | alice |
| 2 | bob |
+---------+-------+
解答：
select
user_id,
# concat(s1,s2...sn) 字符串 s1,s2 等多个字符串合并为一个字符串
concat(
# upper(s) 将字符串转换为大写
# left(s,n) 返回字符串 s 的前 n 个字符
upper(left(name,1)),
# lower(s) 将字符串 s 的所有字母变成小写字母
# substr(s, start, length) 从字符串 s 的 start 位置截取长度为 length 的子字符串
# char_length(s) 返回字符串 s 的字符数
lower(substr(name, 2, char_length(name)))
) as name
from
users
order by
user_id
1527.患某种疾病的患者
患者信息表： patients
+--------------+---------+
| column name | type |
+--------------+---------+
| patient_id | int |
| patient_name | varchar |
| conditions | varchar |
+--------------+---------+
在 sql 中，patient_id （患者 id）是该表的主键。
'conditions' （疾病）包含 0 个或以上的疾病代码，以空格分隔。
这个表包含医院中患者的信息。
查询患有 i 类糖尿病的患者 id （patient_id）、患者姓名（patient_name）以及其患有的所有疾病代码（conditions）。i 类糖尿病的代码总是包含前缀 diab1 。
按 任意顺序 返回结果表。
查询结果格式如下示例所示。
示例 1:
输入：
patients表：
+------------+--------------+--------------+
| patient_id | patient_name | conditions |
+------------+--------------+--------------+
| 1 | daniel | yfev cough |
| 2 | alice | |
| 3 | bob | diab100 myop |
| 4 | george | acne diab100 |
| 5 | alain | diab201 |
+------------+--------------+--------------+
输出：
+------------+--------------+--------------+
| patient_id | patient_name | conditions |
+------------+--------------+--------------+
| 3 | bob | diab100 myop |
| 4 | george | acne diab100 |
+------------+--------------+--------------+
解释：bob 和 george 都患有代码以 diab1 开头的疾病。
解答：
select
*
from
patients
where
conditions like "diab1%"
or
conditions like "% diab1%"
196.删除重复的电子邮件
表: person
+-------------+---------+
| column name | type |
+-------------+---------+
| id | int |
| email | varchar |
+-------------+---------+
id 是该表的主键列(具有唯一值的列)。
该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。
编写解决方案 删除 所有重复的电子邮件，只保留一个具有最小 id 的唯一电子邮件。
（对于 sql 用户，请注意你应该编写一个 delete 语句而不是 select 语句。）
（对于 pandas 用户，请注意你应该直接修改 person 表。）
运行脚本后，显示的答案是 person 表。驱动程序将首先编译并运行您的代码片段，然后再显示 person 表。person 表的最终顺序 无关紧要 。
返回结果格式如下示例所示。
示例 1:
输入:
person 表:
+----+------------------+
| id | email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
输出:
+----+------------------+
| id | email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
解释: john@example.com重复两次。我们保留最小的id = 1。
解答：
delete
p1
from
person p1
,person p2
where
p1.id > p2.id
and
p1.email = p2.email
delete from
person
where
id not in (
select
*
from (
select
min(id)
from person
group by email
) as p1);
176.第二高的薪水
employee 表：
+-------------+------+
| column name | type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
在 sql 中，id 是这个表的主键。
表的每一行包含员工的工资信息。
查询并返回 employee 表中第二高的薪水 。如果不存在第二高的薪水，查询应该返回 null(pandas 则返回 none) 。
查询结果如下例所示。
示例 1：
输入：
employee 表：
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
输出：
+---------------------+
| secondhighestsalary |
+---------------------+
| 200 |
+---------------------+
示例 2：
输入：
employee 表：
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
+----+--------+
输出：
+---------------------+
| secondhighestsalary |
+---------------------+
| null |
+---------------------+
解答：
select
max(salary) as secondhighestsalary
from
employee
where
salary <(
select
max(salary)
from
employee
)
1484.按日期分组销售产品
表 activities：
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| sell_date | date |
| product | varchar |
+-------------+---------+
该表没有主键(具有唯一值的列)。它可能包含重复项。
此表的每一行都包含产品名称和在市场上销售的日期。
编写解决方案找出每个日期、销售的不同产品的数量及其名称。
每个日期的销售产品名称应按词典序排列。
返回按 sell_date 排序的结果表。
结果表结果格式如下例所示。
示例 1:
输入：
activities 表：
+------------+-------------+
| sell_date | product |
+------------+-------------+
| 2020-05-30 | headphone |
| 2020-06-01 | pencil |
| 2020-06-02 | mask |
| 2020-05-30 | basketball |
| 2020-06-01 | bible |
| 2020-06-02 | mask |
| 2020-05-30 | t-shirt |
+------------+-------------+
输出：
+------------+----------+------------------------------+
| sell_date | num_sold | products |
+------------+----------+------------------------------+
| 2020-05-30 | 3 | basketball,headphone,t-shirt |
| 2020-06-01 | 2 | bible,pencil |
| 2020-06-02 | 1 | mask |
+------------+----------+------------------------------+
解释：
对于2020-05-30，出售的物品是 (headphone, basketball, t-shirt)，按词典序排列，并用逗号 ',' 分隔。
对于2020-06-01，出售的物品是 (pencil, bible)，按词典序排列，并用逗号分隔。
对于2020-06-02，出售的物品是 (mask)，只需返回该物品名。
解答：
select
sell_date,
count(distinct product) as num_sold,
group_concat(distinct product order by product) as products
from
activities
group by
sell_date
order by
sell_date
1327.列出指定时间段内所有的下单产品
表: products
+------------------+---------+
| column name | type |
+------------------+---------+
| product_id | int |
| product_name | varchar |
| product_category | varchar |
+------------------+---------+
product_id 是该表主键(具有唯一值的列)。
该表包含该公司产品的数据。
表: orders
+---------------+---------+
| column name | type |
+---------------+---------+
| product_id | int |
| order_date | date |
| unit | int |
+---------------+---------+
该表可能包含重复行。
product_id 是表单 products 的外键（reference 列）。
unit 是在日期 order_date 内下单产品的数目。
写一个解决方案，要求获取在 2020 年 2 月份下单的数量不少于 100 的产品的名字和数目。
返回结果表单的 顺序无要求 。
查询结果的格式如下。
示例 1:
输入：
products 表:
+-------------+-----------------------+------------------+
| product_id | product_name | product_category |
+-------------+-----------------------+------------------+
| 1 | leetcode solutions | book |
| 2 | jewels of stringology | book |
| 3 | hp | laptop |
| 4 | lenovo | laptop |
| 5 | leetcode kit | t-shirt |
+-------------+-----------------------+------------------+
orders 表:
+--------------+--------------+----------+
| product_id | order_date | unit |
+--------------+--------------+----------+
| 1 | 2020-02-05 | 60 |
| 1 | 2020-02-10 | 70 |
| 2 | 2020-01-18 | 30 |
| 2 | 2020-02-11 | 80 |
| 3 | 2020-02-17 | 2 |
| 3 | 2020-02-24 | 3 |
| 4 | 2020-03-01 | 20 |
| 4 | 2020-03-04 | 30 |
| 4 | 2020-03-04 | 60 |
| 5 | 2020-02-25 | 50 |
| 5 | 2020-02-27 | 50 |
| 5 | 2020-03-01 | 50 |
+--------------+--------------+----------+
输出：
+--------------------+---------+
| product_name | unit |
+--------------------+---------+
| leetcode solutions | 130 |
| leetcode kit | 100 |
+--------------------+---------+
解释：
2020 年 2 月份下单 product_id = 1 的产品的数目总和为 (60 + 70) = 130 。
2020 年 2 月份下单 product_id = 2 的产品的数目总和为 80 。
2020 年 2 月份下单 product_id = 3 的产品的数目总和为 (2 + 3) = 5 。
2020 年 2 月份 product_id = 4 的产品并没有下单。
2020 年 2 月份下单 product_id = 5 的产品的数目总和为 (50 + 50) = 100 。
解答：
select
p.product_name,
sum(o.unit) as unit
from
orders o
left join
products p
on
o.product_id = p.product_id
where
date_format(o.order_date,"%y-%m") = "2020-02"
group by
o.product_id
having
sum(o.unit) >= 100
1517.查找拥有有效邮件的用户
表: users
+---------------+---------+
| column name | type |
+---------------+---------+
| user_id | int |
| name | varchar |
| mail | varchar |
+---------------+---------+
user_id 是该表的主键（具有唯一值的列）。
该表包含了网站已注册用户的信息。有一些电子邮件是无效的。
编写一个解决方案，以查找具有有效电子邮件的用户。
一个有效的电子邮件具有前缀名称和域，其中：
- 前缀 名称是一个字符串，可以包含字母（大写或小写），数字，下划线
'_'，点'.'和/或破折号'-'。前缀名称 必须 以字母开头。 - 域 为
'@leetcode.com'。
以任何顺序返回结果表。
结果的格式如以下示例所示：
示例 1：
输入：
users 表:
+---------+-----------+-------------------------+
| user_id | name | mail |
+---------+-----------+-------------------------+
| 1 | winston | winston@leetcode.com |
| 2 | jonathan | jonathanisgreat |
| 3 | annabelle | bella-@leetcode.com |
| 4 | sally | sally.come@leetcode.com |
| 5 | marwan | quarz#2020@leetcode.com |
| 6 | david | david69@gmail.com |
| 7 | shapiro | .shapo@leetcode.com |
+---------+-----------+-------------------------+
输出：
+---------+-----------+-------------------------+
| user_id | name | mail |
+---------+-----------+-------------------------+
| 1 | winston | winston@leetcode.com |
| 3 | annabelle | bella-@leetcode.com |
| 4 | sally | sally.come@leetcode.com |
+---------+-----------+-------------------------+
解释：
用户 2 的电子邮件没有域。
用户 5 的电子邮件带有不允许的 '#' 符号。
用户 6 的电子邮件没有 leetcode 域。
用户 7 的电子邮件以点开头。
解答：
select
user_id,
name,
mail
from
users
where
# 使用 regexp 和 rlike都可进行正则表达式匹配，以'^'开始，以'$'结束
mail rlike "^[a-za-z][a-za-z0-9\\_\\.\\-]*@leetcode\\.com$"
发表评论