前言

一、题目：1407. 排名靠前的旅行者

表：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.正确示范①

提交SQL

select u1.name,
ifnull(sum(distance),0) travelled_distance
from Users u1
left join Rides u2
on u1.id=u2.user_id
group by u1.id,u1.name
order by travelled_distance desc,name;

运行结果

2.正确示范②

提交SQL

select u1.name,
case when sum(distance) is null then 0 else sum(distance) end travelled_distance
from Users u1
left join Rides u2
on u1.id=u2.user_id
group by u1.id,u1.name
order by travelled_distance desc,name;

运行结果

3.正确示范③

提交SQL

select name,ifnull(travelled_distance,0) travelled_distance from(
    select distinct u1.id,u1.name,
    sum(distance) over(partition by u1.id,u1.name) travelled_distance
    from Users u1
    left join Rides u2
    on u1.id=u2.user_id
) u
order by travelled_distance desc,name;

运行结果

4.正确示范④

提交SQL

select name,
case when travelled_distance is null then 0 
    else  travelled_distance end travelled_distance 
from(
    select distinct u1.id,u1.name,
    sum(distance) over(partition by u1.id,u1.name) travelled_distance
    from Users u1
    left join Rides u2
    on u1.id=u2.user_id
) u
order by travelled_distance desc,name;

运行结果

5.其他

总结

正确示范①思路：
group by + sum + ifnull +order by
正确示范②思路：
group by + sum + case when +order by
正确示范③思路：
sum(xx) over(partition by xx) + distinct + ifnull + order by
正确示范④思路：
sum(xx) over(partition by xx) + distinct + case when + order by