Mysql 查询近12个月的数据

SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS `month`,count(*) from hy_infor where DATE_FORMAT(create_date,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 0 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `month`,count(*) from hy_infor where DATE_FORMAT(create_date,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `month`,count(*) from hy_infor where DATE_FORMAT(create_date,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `month`,count(*) from hy_infor where DATE_FORMAT(create_date,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `month`,count(*) from hy_infor where DATE_FORMAT(create_date,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `month`,count(*) from hy_infor where DATE_FORMAT(create_date,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `month`,count(*) from hy_infor where DATE_FORMAT(create_date,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `month`,count(*) from hy_infor where DATE_FORMAT(create_date,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `month`,count(*) from hy_infor where DATE_FORMAT(create_date,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `month`,count(*) from hy_infor where DATE_FORMAT(create_date,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `month`,count(*) from hy_infor where DATE_FORMAT(create_date,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS `month`,count(*) from hy_infor where DATE_FORMAT(create_date,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m')

DATE_FORMAT(CURDATE(), '%Y-%m')当前年、月

DATE_FORMAT(create_date,'%Y-%m')截图数据时间字段年、月=DATE_FORMAT((CURDATE() - INTERVAL 0 MONTH), 对比当前年、月'%Y-%m')

CURDATE() - INTERVAL 0 MONTH//CURDATE当前年月 减去 INTERVAL 0 MONTH月份 0代表当前，1代表上个月，以此类推

输出一下结果