SQL-hard-262-trips and users

SQL刷题，hard-262

[sql hard 262]: https://leetcode.cn/problems/trips-and-users/description/ “sql hard 262”

题目理解

首先Trips表格中有一列为status，其为枚举类，只要不是‘completed’就要拿出来用来计数，作为未经判断过的取消订单表，再取Users表中banned为‘No’的字段，将这两个表用users_id进行求交集，就可以返回‘被司机或乘客取消的非禁止用户生成的订单数量’，然后同样的逻辑，不判断Trips表格中的status就可以返回非禁止用户生成的订单总数，这两个都按照时间排序count一下，相除就可以返回取消率。

尝试解法

select
t4.day as Day,
CAST(ifNULL(t3.CancellationRate, 0)*1./t4.CancellationRate as decimal(8,2)) as 'Cancellation Rate'
from
(select
request_at as Day,
count(request_at) as CancellationRate
from
(
select
client_id,
driver_id,
request_at
from
Trips
where
EXISTS(
  select
  users_id
  from
  Users
  where
  banned = 'No'
  and client_id = users_id
)
and EXISTS(
  select
  users_id
  from
  Users
  where
  banned = 'No'
  and driver_id = users_id
)
)t1
group by
request_at)t4
left join
(select
request_at as Day,
count(request_at) as CancellationRate
from
(
select
client_id,
driver_id,
request_at
from
Trips
where
status <> 'completed'
and EXISTS(
  select
  users_id
  from
  Users
  where
  banned = 'No'
  and client_id = users_id
)
and EXISTS(
  select
  users_id
  from
  Users
  where
  banned = 'No'
  and driver_id = users_id
)
)t2
group by
request_at)t3
on t4.Day = t3.Day
where
t4.day in ('2013-10-01','2013-10-02','2013-10-03')

寻找解法时找到知识点

join语法

join语法之前写起来一直报错，错误还很多，要使用join语法就按照要求将查询写出来

select column1,column2,column3···
from (··· 一个子查询)as t1
left,right,inner,join
(··· 一个子查询) as t2
on t1. = t2. （可以有多个）判断条件
where
group by
等等

具体需要注意的就是子查询要给写明白写清楚，join完后可以再加where和其他判断语句。

EXISTS 与 NOT EXISTS语法

判断一个字段在另一个字段中是否存在。

ifNULL函数的运用

保留小数的方法