每日
SELECT *
FROM
(SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Date
FROM
(SELECT 0 AS a
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) AS a CROSS
JOIN
(SELECT 0 AS a
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) AS b CROSS
JOIN
(SELECT 0 AS a
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) AS c) a
每月
SELECT *
FROM
(SELECT DATE_FORMAT(curdate() - INTERVAL (a.a + (10 * b.a)) MONTH,'%Y-%m') AS Date
FROM
(SELECT 0 AS a
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) AS a CROSS
JOIN
(SELECT 0 AS a
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) AS b) a
每年
SELECT *
FROM
(SELECT DATE_FORMAT(curdate() - INTERVAL (a.a ) YEAR,'%Y') AS Date
FROM
(SELECT 0 AS a
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) AS a) a
后面关联其他表即可实现数据查询
加WHERE即可实现时间段查询,如下,其它时间段同理
每日,最近十个月
DATE_SUB(now(), INTERVAL 10 MONTH)
每月,最近三个月
DATE_FORMAT(DATE_SUB( now(), INTERVAL 3 MONTH),'%Y-%m')
每年,最近两年
DATE_FORMAT(DATE_SUB( now(), INTERVAL 2 YEAR),'%Y')