来源：高频 SQL 50 题（基础版）leetcode.cn/studyplan/s…
620. 有趣的电影
编写解决方案，找出所有影片描述为 非 boring (不无聊) 的并且 id 为奇数 的影片。
返回结果按 rating 降序排列。
结果格式如下示例。
SQL
送分题，多个条件结合 and 判断即可。
select
*
from
cinema
where
description != 'boring'
and id % 2 = 1
order by
rating desc;
1251. 平均售价
编写解决方案以查找每种产品的平均售价。average_price 应该 四舍五入到小数点后两位。
返回结果表 无顺序要求 。
结果格式如下例所示。
思路 + SQL
- 先将出售表格和每个时期的价格表关联起来。关键在于，搜索是否在时间范围内，使用
between A and B来查找。
select
*
from
UnitsSold us
inner join Prices p on us.product_id = p.product_id
and us.purchase_date between p.start_date and p.end_date;
- 用产品的 id 进行分组后，在组内进行计算
select
us.product_id,
round(sum(us.units * p.price) / sum(us.units), 2) as average_price
from
UnitsSold us
inner join Prices p on us.product_id = p.product_id
and us.purchase_date between p.start_date and p.end_date
group by
us.product_id;
- 在提交的时候，发现和题目的理解有问题。题目是希望获取所有的产品的平均售价，也就是没有卖过的商品也需要记录在里边，因为没有卖过，所以记录为 0。这就需要 price 左连接出售记录表了。
select
p.product_id,
round(sum(us.units * p.price) / sum(us.units), 2) as average_price
from
Prices p
left join UnitsSold us on us.product_id = p.product_id
and us.purchase_date between p.start_date and p.end_date
group by
p.product_id;
现在就离正确答案越来越近了：
- 最后使用空值判断函数，如果发现查到的值为空，将其转化为 0。使用 MySQL 中的
IFNULL函数。IFNULL函数接受两个参数，如果第一个参数为 NULL，则返回第二个参数的值。
select
p.product_id,
ifNull(
round(sum(us.units * p.price) / sum(us.units), 2),
0
) as average_price
from
Prices p
left join UnitsSold us on us.product_id = p.product_id
and us.purchase_date between p.start_date and p.end_date
group by
p.product_id;
1075. 项目员工 I
请写一个 SQL 语句，查询每一个项目中员工的 平均 工作年限，精确到小数点后两位。
查询结果的格式如下：
思路 + SQL
- 需要查每个项目中的平均工作年限，首先需要两个表做内连接
select
*
from
Project p
inner join Employee e on p.employee_id = e.employee_id;
- 然后分组进行
avg函数统计即可
select
p.project_id,
round(avg(e.experience_years), 2) as average_years
from
Project p
inner join Employee e on p.employee_id = e.employee_id
group by
p.project_id;
1633. 各赛事的用户注册率
编写解决方案统计出各赛事的用户注册百分率，保留两位小数。
返回的结果表按 percentage 的 降序 排序，若相同则按 contest_id 的 升序 排序。
返回结果如下示例所示。
示例 1：
思路 + SQL
注册率就是在注册表里统计一下赛事订阅的总人数，然后再除以用户的总人数，所以看起来并不需要关联表，也确实是这样的。
select
contest_id,
round(
(count(user_id) * 100) / (
-- 查询赛事订阅总人数
select
count(*)
from
users
),
2
) as percentage
from
Register
group by
contest_id
order by
percentage desc,
contest_id asc;
1211. 查询结果的质量和占比
将查询结果的质量 quality 定义为：
各查询结果的评分与其位置之间比率的平均值。
将劣质查询百分比 poor_query_percentage 为：
评分小于 3 的查询结果占全部查询结果的百分比。
编写解决方案，找出每次的 query_name、quality 和 poor_query_percentage。
quality 和 poor_query_percentage 都应 四舍五入到小数点后两位。
以 任意顺序 返回结果表。
结果格式如下所示：
思路 + SQL 1
用 case ... when 的写法，选择性地选择小于三分的评分进行计数，然后除以总数即可。
select
query_name,
-- 各查询结果的评分与其位置之间比率的平均值
round(avg(rating / position), 2) as quality,
-- 评分小于 3 的查询结果占全部查询结果的百分比
round(
(
count(
case
when rating < 3 then query_name
end
) / count(query_name)
) * 100,
2
) as poor_query_percentage
from
Queries
group by
query_name;
思路 + SQL 2
用 if 来代替 case ... when，评分小于 3 的标记为 1，其他情况标记为 0，然后算平均即可，写法更简约。
select
query_name,
-- 各查询结果的评分与其位置之间比率的平均值
round(avg(rating / position), 2) as quality,
-- 评分小于 3 的查询结果占全部查询结果的百分比
round(avg(if(rating < 3, 1, 0)) * 100, 2) as poor_query_percentage
from
Queries
group by
query_name;
1193. 每月交易 I
编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。
以 任意顺序 返回结果表。
查询结果格式如下所示。
思路 + SQL
这题难在根据月份来分类。这个需要使用一个函数来从日期中提取出年-月出来，然后作为分组的依据之一。
使用 date_format 方法格式化日期：
DATE_FORMAT(date_column, '%Y-%m')
了解这方法后就好搞了，先做个分组测试一下：
select
date_format(trans_date, '%Y-%m') as month,
country
from
Transactions
group by
month,
country;
上面的函数虽然是专门处理日期的，但是写法还是需要特意去记。如果表中的日期的格式是一致的，那完全可以用处理字符串的方法进行处理，更加便捷。
在 SQL 中，可以使用 SUBSTRING 函数来取前 n 个字符。
SUBSTRING(string, start, length)
其中，string 是要截取的字符串，start 是起始位置，length 是要截取的字符数。
所以 年-月 同样可以通过下面的写法获取：
substring(trans_date, 1, 7) as month
年月分组后，进行常规的聚合函数计算：
select
date_format(trans_date, '%Y-%m') as month,
country,
count(state) as trans_count,
sum(if(state = 'approved', 1, 0)) as approved_count,
sum(amount) as trans_total_amount,
sum(if(state = 'approved', amount, 0)) as approved_total_amount
from
Transactions
group by
month,
country;
case when 写法纯纯懒得写就不写了。
刚好学到 count 其实也可以和 if 组合使用，只要把 0 改为 null 就行，因为 count 不数 null。
select
date_format(trans_date, '%Y-%m') as month,
country,
count(state) as trans_count,
-- 使用 count + if 的写法
count(if(state = 'approved', 1, null)) as approved_count,
sum(amount) as trans_total_amount,
-- sum 配合 if 的写法
sum(if(state = 'approved', amount, 0)) as approved_total_amount
from
Transactions
group by
month,
country;
用 count 好像是会比 sum 快一点点。
1174. 即时食物配送 II
如果顾客期望的配送日期和下单日期相同，则该订单称为 「即时订单」，否则称为 「计划订单」。
「首次订单」 是顾客最早创建的订单。我们保证一个顾客只会有一个 「首次订单」。
编写解决方案以获取即时订单在所有用户的首次订单中的比例。保留两位小数。
结果示例如下所示：
思路 + SQL 1
- 先搜索出一个表，这个表用于记录每个顾客的首份订单以及对应的下单时间。
select
customer_id,
min(order_date) as first_order_date
from
Delivery
group by
customer_id;
- 然后让这个表和 Delivery 表做内链接，拓展 customer_pref_delivery_date 字段，获取用户首次订单的期望配送日期。需要特别注意特别注意连接的关联条件：用户 id 相同的同时，也要保证两表的 order_date 相关联。
select
d.customer_id,
d.order_date,
d.customer_pref_delivery_date
from
(
select
customer_id,
min(order_date) as first_order_date
from
Delivery
group by
customer_id
) fo
inner join Delivery d on fo.customer_id = d.customer_id
and fo.first_order_date = d.order_date;
- 最后，统计即时订单（order_date = customer_pref_delivery_date）即可
select
round(
avg(
if(
d.order_date = d.customer_pref_delivery_date,
1,
0
)
) * 100,
2
) as immediate_percentage
from
(
-- 首批订单时间表
select
customer_id,
min(order_date) as first_order_date
from
Delivery
group by
customer_id
) fo
inner join Delivery d on fo.customer_id = d.customer_id
and fo.first_order_date = d.order_date;
思路 + SQL 2
上面使用了内连接的方式，那当然同样的是能转化成 where 语句。
select
round(
avg(
if(
order_date = customer_pref_delivery_date,
1,
0
)
) * 100,
2
) as immediate_percentage
from
Delivery
where
(customer_id, order_date) in (
select
customer_id,
min(order_date) as first_order_date
from
Delivery
group by
customer_id
);
这里使用 in，代表客户 id 和订单时间都要和 in 里的查询匹配上。in 里的查询当然是客户首次订单表。
550. 游戏玩法分析 IV
编写解决方案，报告在首次登录的第二天再次登录的玩家的 比率，四舍五入到小数点后两位。换句话说，你需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。
结果格式如下所示：
思路 + SQL 1
- （错误方法）先查询出符合条件的玩家，即连续有两天以上登录的。这里使用 distinct 的原因是玩家有可能连续登录三四天以上，这样可以避免搜索出重复的玩家 id
select
distinct a1.player_id
from
Activity a1
inner join Activity a2 on a1.player_id = a2.player_id
and datediff(a1.event_date, a2.event_date) = -1;
但这样会发现一个明显的 bug，连续登录是限定于首次登录后进行第二次登录，如果用户登录的天数为 1，5，6，这样也会被搜索出来。
- 所以正确的查询方法是，查询出每个用户的首次登录的时间：
select
player_id,
min(event_date)
from
Activity
group by
player_id;
- 然后上面的查询和 Activity 表做内连接，搜索出真正符合连续登录条件的玩家的 id
select
a.player_id
from
Activity as a
-- 首日登录表
inner join (
select
player_id,
min(event_date) as event_date
from
Activity
group by
player_id
) as b on a.player_id = b.player_id
-- 关联第二天仍然登录的用户
and dateDiff(a.event_date, b.event_date) = 1;
- 最后使用 count 函数进行计数即可。
select
round(
-- 符合首次登录玩家的人数
count(a.player_id) / (
-- 玩家总人数
select
count(distinct player_id)
from
Activity
),
2
) as fraction
from
-- 内连接后的表为首次登录后第二天登录的玩家
Activity as a
inner join (
-- 所有玩家首次登录的时间表
select
player_id,
min(event_date) as event_date
from
Activity
group by
player_id
) as b on a.player_id = b.player_id
and dateDiff(a.event_date, b.event_date) = 1;
思路 + SQL 2
又在解答中看到了有老哥直接使用左关联的方法，也是一个好方法。
- 先过滤出每个用户的首次登录的日期，得到每个用户的首次登录表：
select
player_id,
min(event_date) as event_date
from
Activity
group by
player_id;
- 然后这个登录表和 Activity 做连接，关联条件为首次登录第二天还有登录的记录
select
*
from
(
select
player_id,
min(event_date) as event_date
from
Activity
group by
player_id
) fl
left join Activity a on fl.player_id = a.player_id
and dateDiff(a.event_date, fl.event_date) = 1;
这样获取的结果是每个用户以及是否符合首次登录的第二天再次登录（如果第二天没有登录，Activity 表就不会被关联到）。
- 最后做一下 count 计算就可以得到结果了。写法比第一种还简单。
select
round(count(a.player_id) / count(fl.player_id), 2) as fraction
from
(
select
player_id,
min(event_date) as event_date
from
Activity
group by
player_id
) fl
left join Activity a on fl.player_id = a.player_id
and dateDiff(a.event_date, fl.event_date) = 1;
