以下的sql功能实现了,应该不是最优的语句。
查询最近12个月表虚拟表:
SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS `month`
和业务表关联查询数据进行统计:
SELECT DATE_FORMAT((CURDATE()), '%Y-%m') as name,count(DISTINCT(car_number)) as count FROM saas_car_isu where DATE_FORMAT(isu_start_date,'%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')
UNION
SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') as name,count(DISTINCT(car_number)) as count FROM saas_car_isu where DATE_FORMAT(isu_start_date,'%Y-%m') = DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m')
UNION
SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') as name,count(DISTINCT(car_number)) as count FROM saas_car_isu where DATE_FORMAT(isu_start_date,'%Y-%m') = DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m')
UNION
SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') as name,count(DISTINCT(car_number)) as count FROM saas_car_isu where DATE_FORMAT(isu_start_date,'%Y-%m') = DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m')
UNION
SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') as name,count(DISTINCT(car_number)) as count FROM saas_car_isu where DATE_FORMAT(isu_start_date,'%Y-%m') = DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m')
UNION
SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') as name,count(DISTINCT(car_number)) as count FROM saas_car_isu where DATE_FORMAT(isu_start_date,'%Y-%m') = DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m')
UNION
SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') as name,count(DISTINCT(car_number)) as count FROM saas_car_isu where DATE_FORMAT(isu_start_date,'%Y-%m') = DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m')
UNION
SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') as name,count(DISTINCT(car_number)) as count FROM saas_car_isu where DATE_FORMAT(isu_start_date,'%Y-%m') = DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m')
UNION
SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') as name,count(DISTINCT(car_number)) as count FROM saas_car_isu where DATE_FORMAT(isu_start_date,'%Y-%m') = DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m')
UNION
SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') as name,count(DISTINCT(car_number)) as count FROM saas_car_isu where DATE_FORMAT(isu_start_date,'%Y-%m') = DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m')
UNION
SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') as name,count(DISTINCT(car_number)) as count FROM saas_car_isu where DATE_FORMAT(isu_start_date,'%Y-%m') = DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m')
UNION
SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') as name,count(DISTINCT(car_number)) as count FROM saas_car_isu where DATE_FORMAT(isu_start_date,'%Y-%m') = DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m')