Mysql每日一题（行程与用户，困难※）

 今天给大家分享一个截止到目前位置，我遇到最难的一道mysql题目，非常建议大家亲手做一遍

 

 完整代码如下，这道题的主要难点是它有两个外键，以前没遇到过，我也没当回事，分享一下错误经验哈

当时我写的where判断条件是

where Users.banned="No" and(Trips.client_id=Users.users_id or Trips.driver_id=Users.users_id)

当时我就认为和下面代码写的

from Trips as T

inner join Users as U1 on T.client_id=U1.users_id

inner join Users as U2 on T.driver_id=U2.users_id

where U1.banned="No" and U2.banned="No"一样

我觉得系统应该会在我的系统应该像循环一样再判断完Trips.client_id=Users.users_id or 后，帮我分别判断一下

Trips.client_id=Users.users_id的时候Users.banned="No"？

Trips.driver_id=Users.users_id的时候Users.banned="No"？

结果并不会，给大家看一些官方题解，其中就介绍了这个问题

 

 反正我是狠狠的跳进了这个设计好的坑里，一直爬不出来，知道看了题解，这个题目我觉得很好，以后我希望我会经常拿出来看看。

select T2.request_at as "Day",ifnull(round(T1.c/T2.c,2),0) as "Cancellation Rate"
from (select T.request_at,ifnull(count(*),0) as c
from Trips as T 
inner join Users as U1 on T.client_id=U1.users_id
inner join Users as U2 on T.driver_id=U2.users_id
where U1.banned="No" and U2.banned="No"
group by T.request_at) as T2 left join 
(select T.request_at,ifnull(count(*),0) as c
from Trips as T 
inner join Users as U1 on T.client_id=U1.users_id
inner join Users as U2 on T.driver_id=U2.users_id
where U1.banned="No" and U2.banned="No" and T.status Like "ca%"
group by T.request_at) as T1 on T1.request_at=T2.request_at;