Mysql获取当前时间前12个月份列表
SELECT DATE_FORMAT(DATE_ADD(DATE_ADD(CURDATE(), INTERVAL -12 MONTH), INTERVAL ROW MONTH),'%Y-%m') DATE FROM
(
SELECT @ROW := @ROW + 1 AS ROW FROM
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t,
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2,
(SELECT @ROW:=-1) r
) se
WHERE DATE_FORMAT(DATE_ADD(DATE_ADD(CURDATE(), INTERVAL -12 MONTH), INTERVAL ROW MONTH),'%Y-%m') <= DATE_FORMAT(NOW(),'%Y-%m')
//---------------------------------------------
mySql查询近12个月每个月访客人数,一条sql语句搞定
SELECT DATE_FORMAT(CURDATE()- INTERVAL 11 MONTH, '%Y-%m') AS `month`,COUNT(*) FROM VisitInfo WHERE DATE_FORMAT(V_StDate,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `month`,COUNT(*) FROM VisitInfo WHERE DATE_FORMAT(V_StDate,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `month`,COUNT(*) FROM VisitInfo WHERE DATE_FORMAT(V_StDate,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `month`,COUNT(*) FROM VisitInfo WHERE DATE_FORMAT(V_StDate,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `month`,COUNT(*) FROM VisitInfo WHERE DATE_FORMAT(V_StDate,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `month`,COUNT(*) FROM VisitInfo WHERE DATE_FORMAT(V_StDate,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `month`,COUNT(*) FROM VisitInfo WHERE DATE_FORMAT(V_StDate,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `month`,COUNT(*) FROM VisitInfo WHERE DATE_FORMAT(V_StDate,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `month`,COUNT(*) FROM VisitInfo WHERE DATE_FORMAT(V_StDate,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `month`,COUNT(*) FROM VisitInfo WHERE DATE_FORMAT(V_StDate,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `month`,COUNT(*) FROM VisitInfo WHERE DATE_FORMAT(V_StDate,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m')
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 0 MONTH), '%Y-%m') AS `month`,COUNT(*) FROM VisitInfo WHERE DATE_FORMAT(V_StDate,'%Y-%m')=DATE_FORMAT((CURDATE() - INTERVAL 0 MONTH), '%Y-%m')
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)