官方题解 | #某滴出行8月和9月北京市的新司机数#
某滴出行8月和9月北京市的新司机数
http://www.nowcoder.com/practice/2e975184b6fc4695b2ba68ce8facaf20
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';
