- 学习:知识的初次邂逅
- 复习:知识的温故知新
练习:知识的实践应用
一,原题力扣链接
二,题干
表:
Users
+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | name | varchar | +---------------+---------+ id 是该表中具有唯一值的列。 name 是用户名字。表:
Rides
+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | user_id | int | | distance | int | +---------------+---------+ id 是该表中具有唯一值的列。 user_id 是本次行程的用户的 id, 而该用户此次行程距离为 distance 。编写解决方案,报告每个用户的旅行距离。
返回的结果表单,以
travelled_distance
降序排列 ,如果有两个或者更多的用户旅行了相同的距离, 那么再以name
升序排列 。返回结果格式如下例所示。
示例 1:
输入: Users 表: +------+-----------+ | id | name | +------+-----------+ | 1 | Alice | | 2 | Bob | | 3 | Alex | | 4 | Donald | | 7 | Lee | | 13 | Jonathan | | 19 | Elvis | +------+-----------+ Rides 表: +------+----------+----------+ | id | user_id | distance | +------+----------+----------+ | 1 | 1 | 120 | | 2 | 2 | 317 | | 3 | 3 | 222 | | 4 | 7 | 100 | | 5 | 13 | 312 | | 6 | 19 | 50 | | 7 | 7 | 120 | | 8 | 19 | 400 | | 9 | 7 | 230 | +------+----------+----------+ 输出: +----------+--------------------+ | name | travelled_distance | +----------+--------------------+ | Elvis | 450 | | Lee | 450 | | Bob | 317 | | Jonathan | 312 | | Alex | 222 | | Alice | 120 | | Donald | 0 | +----------+--------------------+ 解释: Elvis 和 Lee 旅行了 450 英里,Elvis 是排名靠前的旅行者,因为他的名字在字母表上的排序比 Lee 更小。 Bob, Jonathan, Alex 和 Alice 只有一次行程,我们只按此次行程的全部距离对他们排序。 Donald 没有任何行程, 他的旅行距离为 0。
三,建表语句
Create Table If Not Exists Users (id int, name varchar(30));
Create Table If Not Exists Rides (id int, user_id int, distance int);
Truncate table Users;
insert into Users (id, name) values ('1', 'Alice');
insert into Users (id, name) values ('2', 'Bob');
insert into Users (id, name) values ('3', 'Alex');
insert into Users (id, name) values ('4', 'Donald');
insert into Users (id, name) values ('7', 'Lee');
insert into Users (id, name) values ('13', 'Jonathan');
insert into Users (id, name) values ('19', 'Elvis');
Truncate table Rides;
insert into Rides (id, user_id, distance) values ('1', '1', '120');
insert into Rides (id, user_id, distance) values ('2', '2', '317');
insert into Rides (id, user_id, distance) values ('3', '3', '222');
insert into Rides (id, user_id, distance) values ('4', '7', '100');
insert into Rides (id, user_id, distance) values ('5', '13', '312');
insert into Rides (id, user_id, distance) values ('6', '19', '50');
insert into Rides (id, user_id, distance) values ('7', '7', '120');
insert into Rides (id, user_id, distance) values ('8', '19', '400');
insert into Rides (id, user_id, distance) values ('9', '7', '230');
四,分析
题解:
第一张表:用户表
字段:用户id,用户姓名
第二张表:旅行者表
字段:id,用户id,用户行程
题目要求:
编写解决方案,报告每个用户的旅行距离。
返回的结果表单,以
travelled_distance
降序排列 ,如果有两个或者更多的用户旅行了相同的距离, 那么再以name
升序排列 。
分析:
第一步,开窗---- 开窗之后再去重 其实大可不必
所以去掉第一步,直接以用户id分组 sum 每个用户的距离 然后去用户id和sum距离字段
第三步,拼接2个表。用用户表 左连接 第二步生成的表 左连接可以拿到有的没有行程的用户
如果没有形成 这是null 我if判断一下 如果是null 就变成0
最后以 距离降序,以姓名升序
五,SQL解答
with t1 as (
select user_id,sum(distance) as sum_d from Rides group by user_id
)
select name,if(sum_d is null,0,sum_d) as travelled_distance
from users u left join t1 on u.id=t1.user_id
order by travelled_distance desc,name;
六,验证
七,知识点总结
- 分组,聚合练习
- 左连接练习,左表和右表中间的数据+左表的数据,右表没有数据则显示为null
- if判断语句练习
- is null 判断语句练习
- 学习:知识的初次邂逅
- 复习:知识的温故知新
练习:知识的实践应用