题目链接:点击打开链接
题目大意:略。
解题思路:题目理解起来不难,就 P 事情特别多。
AC 代码
WITH RECURSIVE cal AS( SELECT 1 month UNION ALL SELECT month + 1 FROM cal WHERE month < 12 ), # 计算 active_drivers t1 AS(SELECT month, COUNT(*) active_drivers FROM cal LEFT JOIN Drivers ON DATEDIFF(CONCAT('2020-', month, '-1'), DATE_FORMAT(join_date, '%Y-%m-1')) >= 0 WHERE join_date < '2021-1-1' GROUP BY month), # 计算 accepted_rides t2 AS(SELECT month, COUNT(IF(month = MONTH(requested_at), 1, NULL)) accepted_rides FROM cal LEFT JOIN Drivers ON DATEDIFF(CONCAT('2020-', month, '-1'), DATE_FORMAT(join_date, '%Y-%m-1')) >= 0 LEFT JOIN (Rides JOIN AcceptedRides USING(ride_id)) USING(driver_id) WHERE requested_at BETWEEN '2020-01-01' AND '2020-12-31' GROUP BY month), # 合并 t3 AS(SELECT month, active_drivers, 0 accepted_rides FROM t1 UNION ALL SELECT month, 0 active_drivers, accepted_rides FROM t2 UNION ALL SELECT month, 0 active_drivers, 0 accepted_rides FROM cal) SELECT month, SUM(active_drivers) active_drivers, SUM(accepted_rides) accepted_rides FROM t3 GROUP BY month ORDER BY month