MYSQL 获取最近多少天时间列表

1、首先获取一个最近1000天的时间列表，如果不够可以按规则再加

SELECT   adddate(CURDATE(),-(t2.i * 100 + t1.i * 10 + t0.i)) date 
 FROM (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t0,
    (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
    (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2

2、然后获取最近30天的开始时间 DATE_SUB(CURDATE(), INTERVAL 30 DAY)

3、最后截取最近30天的时间，在步骤1的外面包一层然后 BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY)  AND CURDATE()就可以了。

SELECT * FROM 
(SELECT   adddate(CURDATE(),-(t2.i * 100 + t1.i * 10 + t0.i)) date 
FROM (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t0,
    (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
    (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2) date
WHERE date.date BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY)  AND CURDATE();

 

效果是