官方题解 | #某滴出行8月和9月北京市的新司机数#

step1：将日期限定为8月、首次完成订单的时间为8月、地点在北京，查找所有不重复的司机数；

step2：将日期限定为9月、首次完成订单的时间为9月、地点在北京，查找所有不重复的司机数；

step3：将step1和step2的两表全连接。

SELECT DATE_FORMAT(dt,'%Y-%m') AS 'month',COUNT(DISTINCT a.driver_id) as 'driver_cnt' FROM driver_tb a

LEFT JOIN city_tb b on a.city_id=b.city_id WHERE b.city_name='北京' AND DATE_FORMAT(finish_dt,'%Y-%m')='2021-08' AND a.dt BETWEEN '2021-08-01' and '2021-08-31'

UNION

SELECT DATE_FORMAT(dt,'%Y-%m') AS 'month',COUNT(DISTINCT a.driver_id) as 'driver_cnt' FROM driver_tb a LEFT JOIN city_tb b on a.city_id=b.city_id WHERE b.city_name='北京' AND DATE_FORMAT(finish_dt,'%Y-%m')='2021-09' AND a.dt BETWEEN '2021-09-01' and '2021-09-30';