Mysql基础练习题 1407.排名靠前的旅行者（力扣）

编写解决方案，报告每个用户的旅行距离。 # 返回的结果表单，以 travelled_distance 降序排列 ，如果有两个或者更多的用户旅行了相同的距离, 那么再以 name 升序排列 。

题目链接：

https://leetcode.cn/problems/top-travellers/description/

建表插入数据：

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')

画图分析：

代码实现：

#方式一：
select name,if(sum(distance) is null ,0,sum(distance)) as travelled_distance
from users s left join rides r
    on s.id=r.user_id
group by s.id,s.name
order by travelled_distance desc,s.name ;
#方式二：
select name,ifnull(sum(distance),0) as travelled_distance
from users s left join rides r
    on s.id=r.user_id
group by s.id,name
order by travelled_distance desc,name;
#方式三：
select name,coalesce(sum(distance),0) as travelled_distance
from users s left join rides r
    on s.id=r.user_id
group by s.id,name
order by travelled_distance desc,name;