一、问题描述
表：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 。
复制代码
写一段 SQL , 报告每个用户的旅行距离。
返回的结果表单，以 travelled_distance 降序排列 ，如果有两个或者更多的用户旅行了相同的距离, 那么再以 name 升序排列 。
查询结果格式如下例所示。
题目链接：排名靠前的旅行者
二、题目要求
样例
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 |+------+----------+----------+
Result 表：+----------+--------------------+| 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。
复制代码
考察
1.左外连接、聚合函数2.建议用时10~25min
复制代码
三、问题分析
这一题可以使用左外连接，常用的如下：
A inner join B：内连接，取交集
A left join B：左外连接，取 A 全部，B 没有对应的值，则为null
A right join B：右外连接，取 B 全部，A 没有对应的值，则为null
A full outer join B：全外连接，取并集，彼此没有对应的值为null
这一题除了用到左外连接之外，还需要注意下面几个要点：
1.输出的结果，当里程数目为0的时候，只使用sum函数输出的的结果是null，所以我们要在开始if判断里程数目是否为null
2.聚合我一开始是按照name开始聚合的，但测试用例出现了重名的现象，所以要按照id来聚合才行。
四、编码实现
select u.name, ifnull(sum(r.distance),0) as 'travelled_distance'from Users u left join Rides r on u.id=r.user_idgroup by user_idorder by travelled_distance desc,u.name asc
复制代码
五、测试结果
评论