目录
1173. 即时食物配送 I
1174. 即时食物配送 II
1193. 每月交易 I
1194. 锦标赛优胜者
1204. 最后一个能进入电梯的人
1205. 每月交易II
1211. 查询结果的质量和占比
1212. 查询球队积分
1225. 报告系统状态的连续日期
1241. 每个帖子的评论数
1251. 平均售价
1264. 页面推荐
1270. 向公司CEO汇报工作的所有人
1280. 学生们参加各科测试的次数
1285. 找到连续区间的开始和结束数字
1173. 即时食物配送 I
- 源代码:
select round(sum(case when order_date = customer_pref_delivery_date then 1 else 0 end) / count(delivery_id) * 100, 2) as immediate_percentage from delivery
- 思路:
sum() + case when
,使用这种方法让这个题目简化了很多,这也是一种经验吧
1174. 即时食物配送 II
- 源代码:
with first_order as ( select delivery_id from delivery where (customer_id, order_date) in ( select customer_id, min(order_date) from delivery group by customer_id ) ) select round( sum(case when order_date=customer_pref_delivery_date and delivery_id in (select * from first_order) then 1 else 0 end) / (select count(*) from first_order) * 100 , 2) as immediate_percentage from delivery
- 思路:理解需求很重要,这个题目就是要求首次下单中即时订单的占比,因此我们可以先求出首次下单有哪些,然后利用子查询判断既是即时订单又是首次下单的订单个数
1193. 每月交易 I
- 源代码:
select left(trans_date, 7) as month, country, count(id) as trans_count, count(case when state = 'approved' then 1 else null end) as approved_count, sum(amount) as trans_total_amount, sum(case when state = 'approved' then amount else 0 end) as approved_total_amount from transactions group by left(trans_date, 7), country
- 思路:首先我们得知道如何获取到字符串前面的日期,可以有两种方式
substr() / left()
;然后弄清楚分组字段,然后进行聚合就行了
1194. 锦标赛优胜者
- 源代码:
select group_id, id player_id from ( select group_id, id, row_number() over(partition by group_id order by total_score desc, id) rk from ( select id, sum(score) total_score from ( select match_id, first_player id, first_score score from matches union all select match_id, second_player id, second_score score from matches ) t group by id ) t join players on t.id = players.player_id ) t where rk = 1
- 思路:根据题目说到的需要对选手的得分进行累计,也就是不管它作为第一个选手还是第二个选手都需要进行累计,所以我们可以将一张表拆分为两张表来进行
sum
,这样就可以求到每个选手累计的分数,然后再去求组内分数最高的选手
1204. 最后一个能进入电梯的人
- 源代码:
select person_name from ( select person_name, sum(weight) over(order by turn) sum_weight from queue ) t where sum_weight <= 1000 order by sum_weight desc limit 1;
- 思路:对
sum
函数进行开窗,就可以得到累计的体重和,只要取出小于等于1000且最大的那个人,就是最后一个体重合适并进入电梯的人
1205. 每月交易II **
- 源代码:
with base as ( select 'approved' tag, country, substr(trans_date, 1, 7) month, amount from Transactions where state = 'approved' union all select 'chargeback' tag, t.country, substr(cb.trans_date, 1, 7) month, t.amount from Chargebacks cb join Transactions t on cb.trans_id = t.id ) select month, country, sum(if(tag = 'approved', 1, 0)) approved_count, sum(if(tag = 'approved', amount, 0)) approved_amount, sum(if(tag = 'chargeback', 1, 0)) chargeback_count, sum(if(tag = 'chargeback', amount, 0)) chargeback_amount from base group by month, country;
- 思路: 先对数据打上标签,然后
union all
,再进行分组聚合; - 重点学习一下本题的思路,我记得 求直播间最大连续在线人数 也是这个思路
1211. 查询结果的质量和占比
- 源代码:
select query_name, round(avg(rating / position), 2) quality, round(sum(case when rating < 3 then 1 else 0 end) / count(*) * 100, 2) poor_query_percentage from queries group by query_name
- 思路:需求比较简单,没有特别不容易懂的逻辑
1212. 查询球队积分
- 源代码:
select team_id, team_name, ifnull(score, 0) as num_points from teams left join ( select id, sum(score) score from ( -- 作为主场 select host_team id, sum(case when host_goals > guest_goals then 3 when host_goals = guest_goals then 1 else 0 end) score from matches group by id union all -- 作为客场 select guest_team id, sum(case when host_goals < guest_goals then 3 when host_goals = guest_goals then 1 else 0 end) score from matches group by id ) t group by id ) t on teams.team_id = t.id order by num_points desc, team_id;
- 思路:核心逻辑就是
left join
右边的逻辑,我们首先计算主场的得分,然后计算客场的得分,最后将这两部分累计到一起
1225. 报告系统状态的连续日期 **
- 源代码:
select type period_state , min(dt) start_date, max(dt) end_date from ( select type, dt, subdate(dt, row_number() over(partition by type order by dt)) diff from ( select 'succeeded' type, success_date dt from succeeded where success_date between '2019-01-01' and '2019-12-31' union select 'failed' type, fail_date dt from failed where fail_date between '2019-01-01' and '2019-12-31' ) t ) t group by type, diff order by start_date;
- 思路:
类似于求最大连续登陆天数问题
,不同的是本题求 连续登陆的起止时间!这个题目需要对两张表进行连接,然后需要自己创建一个字段(想法非常好),然后再套用最大连续登陆问题套路date_sub + row_number
来解决
1241. 每个帖子的评论数
- 源代码:
select sub_id post_id, ifnull(number_of_comments, 0) number_of_comments from ( select distinct sub_id from submissions where parent_id is null ) t1 left join ( select parent_id, count(distinct sub_id) number_of_comments from submissions where parent_id is not null group by parent_id ) t2 on t1.sub_id = t2.parent_id order by post_id;
- 思路:只要分清楚每一条数据是帖子,还是评论就可以了;然后还要注意去重!
1251. 平均售价
- 源代码:
select product_id, round(sum(price * units) / sum(units), 2) average_price from ( select p.product_id, price, units from prices p join unitssold u on p.product_id = u.product_id and purchase_date between start_date and end_date ) t group by product_id;
- 思路:比较简单
1264. 页面推荐
- 源代码:
select distinct page_id as recommended_page from ( select page_id from likes where user_id in ( select case when user1_id = 1 then user2_id else user1_id end from friendship where user1_id = 1 or user2_id = 1 ) ) t where page_id not in ( select page_id from likes where user_id = 1 ) ;
- 思路:比较简单,需要对其中一些细节注意一下就可以了
1270. 向公司CEO汇报工作的所有人 *
- 源代码:
select e3.employee_id from employees e1, employees e2, employees e3 where -- 根据题目说的要找出所有经理为1的员工id e1.manager_id = 1 and -- 根据上面一个条件找出直接经理为1的员工id,然后让这些员工id作为第二张表的经理,来获取所有的员工id e2.manager_id = e1.employee_id and -- 根绝上面一个条件找出直接或者间接一次得到的所有员工id,然后让这些员工id作为第三张表的经理,来获取所有的员工id e3.manager_id = e2.employee_id and -- 排除自己的经理就是自己的员工id e3.employee_id != 1 ;
- 思路:这个题目如果不写程序很容易得到答案,但是通过程序实现就比较难了,我在上述源码中解释了
where
后面的每一句话的意思
1280. 学生们参加各科测试的次数
- 源代码:
select t1.student_id, student_name, t1.subject_name, count(ex.student_id) attended_exams from ( select student_id, student_name, subject_name from students join subjects on 1 = 1 ) t1 left join examinations ex on t1.student_id = ex.student_id and t1.subject_name = ex.subject_name group by t1.student_id, t1.subject_name order by t1.student_id, t1.subject_name;
- 思路:其中涉及到一个笛卡尔积连接,可以使用
cross join
1285. 找到连续区间的开始和结束数字
- 源代码:
select min(log_id) start_id, max(log_id) end_id from ( select log_id, (log_id - row_number() over(order by log_id)) diff from logs ) t group by diff order by start_id
- 思路:和上面一个题一模一样,求连续登陆的最大时间和最小时间