LeetCode 262. Trips and Users

来源：互联网发布：融资租赁数据编辑：程序博客网时间：2024/05/19 05:02

select allo.r, round(ifnull(canceled.c/allo.c,0),2) from(select count(1) as c,Request_at as r from Trips t,Users u where t.Client_Id = u.Users_Id and u.Banned = 'No' and t.Request_at between '2013-10-01' and '2013-10-03' and t.Status != 'completed' group by Request_at) canceled right join (select count(1) as c,Request_at as r from Trips t,Users u where t.Client_Id = u.Users_Id and u.Banned = 'No' and t.Request_at between '2013-10-01' and '2013-10-03' group by Request_at) allo on canceled.r = allo.r

需要注意的是：

Leetcode的sql是认大小写的
as 后面的重命名字符串如果有空格需要用单引号圈起来，如’ Cancellation Rate ‘
表名列名不能重命名成all
答案中需要保留两位

附上数据库脚本：

+----+-----------+-----------+---------+--------------------+----------+| Id | Client_Id | Driver_Id | City_Id |        Status      |Request_at|+----+-----------+-----------+---------+--------------------+----------+| 1  |     1     |    10     |    1    |     completed      |2013-10-01|| 2  |     2     |    11     |    1    | cancelled_by_driver|2013-10-01|| 3  |     3     |    12     |    6    |     completed      |2013-10-01|| 4  |     4     |    13     |    6    | cancelled_by_client|2013-10-01|| 5  |     1     |    10     |    1    |     completed      |2013-10-02|| 6  |     2     |    11     |    6    |     completed      |2013-10-02|| 7  |     3     |    12     |    6    |     completed      |2013-10-02|| 8  |     2     |    12     |    12   |     completed      |2013-10-03|| 9  |     3     |    10     |    12   |     completed      |2013-10-03| | 10 |     4     |    13     |    12   | cancelled_by_driver|2013-10-03|+----+-----------+-----------+---------+--------------------+----------+create table Trip (    Id Integer Primary Key,    Client_Id Integer,    Driver_Id Integer,    City_Id Integer,    Status varchar(255),    Request_at DATE);+----------+--------+--------+| Users_Id | Banned |  Role  |+----------+--------+--------+|    1     |   No   | client ||    2     |   Yes  | client ||    3     |   No   | client ||    4     |   No   | client ||    10    |   No   | driver ||    11    |   No   | driver ||    12    |   No   | driver ||    13    |   No   | driver |+----------+--------+--------+create table Users (    Users_Id Integer Primary Key,    Banned varchar(255),    Role varchar(255));

0 0