qq_45189757
夏尔勒克莱尔
2019-06-11 16:50

请问这题用 SQL server 怎么解?

  • sql

假设以下为嘀嘀打车的行程表 Trips,Id 是唯一键,Client_Id、Driver_Id 分别是乘客和司机的编号,Status 的三种状态分别意思为 完成、乘客取消、司机取消
+----+-----------+-----------+---------+--------------------+----------+
| Id | Client_Id | Driver_Id | City_Id | Status |Request_at|
+----+-----------+-----------+---------+--------------------+----------+
| 1 | 1 | 10 | 1 | completed |2018-10-01|
| 2 | 2 | 11 | 1 | cancelled_by_driver |2018-10-01|
| 3 | 3 | 12 | 6 | completed |2018-10-01|
| 4 | 4 | 13 | 6 | cancelled_by_client |2018-10-01|
| 5 | 1 | 10 | 1 | completed |2018-10-02|
| 6 | 2 | 11 | 6 | completed |2018-10-02|
| 7 | 3 | 12 | 6 | completed |2018-10-02|
| 8 | 2 | 12 | 12 | completed |2018-10-03|
| 9 | 3 | 10 | 12 | completed |2018-10-03|
|10 | 4 | 13 | 12 | cancelled_by_driver |2018-10-03|
+----+-----------+-----------+---------+--------------------+----------+

Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止,Role 则是一个表示 乘客、司机 的类型。
+----------+--------+--------+
| 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 |
+----------+--------+--------+

写一段 SQL 语句查出 2018年10月1日 至 2018年10月3日 期间非禁止乘客的取消率。基于上表,你的 SQL 语句应返回如下结果,结果保留两位小数。
+------------+-------------------+
| Day | CancellationRate |
+------------+-------------------+
| 2018-10-01 | 0.33 |
| 2018-10-02 | 0.00 |
| 2018-10-03 | 0.50 |
+------------+-------------------+

  • 点赞
  • 回答
  • 收藏
  • 复制链接分享

1条回答