2022-12-11：行程和用户。以下为输出结果，请问 sql 语句如何写？ +------------+-------------------+ | Day | Cancellation

2022-12-11
北京
本文字数：1274 字
阅读完需：约 4 分钟

2022-12-11：行程和用户。以下为输出结果，请问 sql 语句如何写？+------------+-------------------+| Day | Cancellation Rate |+------------+-------------------+| 2013-10-01 | 0.33 || 2013-10-02 | 0.00 || 2013-10-03 | 0.50 |+------------+-------------------+

DROP TABLE IF EXISTS `trips`;CREATE TABLE `trips` (  `id` int(11) NOT NULL,  `client_id` int(11) NOT NULL,  `driver_id` int(11) NOT NULL,  `city_id` int(11) NOT NULL,  `status` enum('cancelled_by_client','cancelled_by_driver','completed') NOT NULL,  `request_at` date NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;INSERT INTO `trips` VALUES ('1', '1', '10', '1', 'completed', '2013-10-01');INSERT INTO `trips` VALUES ('2', '2', '11', '1', 'cancelled_by_driver', '2013-10-01');INSERT INTO `trips` VALUES ('3', '3', '12', '6', 'completed', '2013-10-01');INSERT INTO `trips` VALUES ('4', '4', '13', '6', 'cancelled_by_client', '2013-10-01');INSERT INTO `trips` VALUES ('5', '1', '10', '1', 'completed', '2013-10-02');INSERT INTO `trips` VALUES ('6', '2', '11', '6', 'completed', '2013-10-02');INSERT INTO `trips` VALUES ('7', '3', '12', '6', 'completed', '2013-10-02');INSERT INTO `trips` VALUES ('8', '2', '12', '12', 'completed', '2013-10-03');INSERT INTO `trips` VALUES ('9', '3', '10', '12', 'completed', '2013-10-03');INSERT INTO `trips` VALUES ('10', '4', '13', '12', 'cancelled_by_driver', '2013-10-03');
DROP TABLE IF EXISTS `users`;CREATE TABLE `users` (  `users_id` int(11) NOT NULL,  `banned` enum('No','Yes') NOT NULL,  `role` enum('partner','driver','client') NOT NULL,  PRIMARY KEY (`users_id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `users` VALUES ('1', 'No', 'client');INSERT INTO `users` VALUES ('2', 'Yes', 'client');INSERT INTO `users` VALUES ('3', 'No', 'client');INSERT INTO `users` VALUES ('4', 'No', 'client');INSERT INTO `users` VALUES ('10', 'No', 'driver');INSERT INTO `users` VALUES ('11', 'No', 'driver');INSERT INTO `users` VALUES ('12', 'No', 'driver');INSERT INTO `users` VALUES ('13', 'No', 'driver');

复制代码

答案 2022-12-11：

sql 语句如下：

SELECT T.request_at AS `Day`,   ROUND(      SUM(        IF(T.STATUS = 'completed',0,1)      )      /       COUNT(T.STATUS),      2  ) AS `Cancellation Rate`FROM trips AS TWHERE T.Client_Id NOT IN (  SELECT users_id  FROM users  WHERE banned = 'Yes')ANDT.Driver_Id NOT IN (  SELECT users_id  FROM users  WHERE banned = 'Yes')AND T.request_at BETWEEN '2013-10-01' AND '2013-10-03'GROUP BY T.request_at