mysql生成近30天的所有日期
时间: 2023-02-26 17:56:19 浏览: 51
您可以使用以下 MySQL 查询来生成近 30 天的所有日期:
```
SELECT DATE_ADD(CURDATE(), INTERVAL -30 + NUM DAY) AS date
FROM (
SELECT @i := @i + 1 AS NUM
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
) a, (
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
) b, (
SELECT @i := 0
) c
) d
WHERE NUM <= 30
```
这个查询使用了 `DATE_ADD` 函数来计算从当前日期开始的每一天的日期,并且使用了一个内部的查询来生成数字 1 到 30 的序列,这个序列用于控制计算出的天数。最终,查询会返回一个近 30 天内每一天的日期。
相关问题
mysql生成近30天的所有日期字符串集合
可以使用MySQL的日期函数和子查询来生成近30天的所有日期字符串集合,例如:
```
SELECT DATE_FORMAT(date, '%Y-%m-%d') AS date_str
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
) AS dates
WHERE date BETWEEN CURDATE() - INTERVAL 29 DAY AND CURDATE()
ORDER BY date;
```
这个查询会生成当前日期往前的30天内的所有日期字符串集合,格式为`YYYY-MM-DD`。
mysql查询近30天日期
可以使用以下SQL查询近30天的日期:
```
SELECT DATE_SUB(CURDATE(), INTERVAL 30 DAY) AS start_date, CURDATE() AS end_date;
```
这将返回一个包含起始日期和结束日期的结果集,例如:
```
+------------+------------+
| start_date | end_date |
+------------+------------+
| 2021-10-07 | 2021-11-06 |
+------------+------------+
```
这里使用了`DATE_SUB`函数来从当前日期(`CURDATE()`)减去30天,得到起始日期。同时,使用`CURDATE()`函数获取当前日期作为结束日期。