方式一:
SELECT
a.Date
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
WHERE
a.Date BETWEEN '2019-12-01'
AND '2019-12-30'
方式二:
SET @beginDate = '2017-07-01';
SET @maxDate = '2017-07-31';
SELECT
DATE( @tempDay ),
@tempDay := DATE_ADD( @tempDay, INTERVAL 1 DAY )
FROM
(SELECT
a
FROM
( SELECT '1' AS a UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' ) AS a
JOIN ( SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' ) AS b ON 1 ) f #该表是系统上已存在的一个有比较多数据的表 弄个临时表
LEFT JOIN ( SELECT @tempDay := @beginDate ) b ON 1 = 1
WHERE
@tempDay <= @maxDate
方式三:
SET @days = TIMESTAMPDIFF( DAY, DATE_ADD( CURDATE(), INTERVAL - 1 MONTH ), CURDATE() )+0; #表示最近一个月 @days值可以自己定义
SET @number = 1;
SELECT
t.datameth AS 'datameths'
FROM
(
SELECT
@number := @number - 1 AS 'numberbvalue',
ADDDATE( CURDATE(), @number ) AS 'datameth'
FROM
(
SELECT
a
FROM
( SELECT '1' AS a UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' ) AS a
JOIN ( SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' ) AS b ON 1 #里面会自动生成36条数据,也就是类似于一个表。
) e
WHERE @number < @days ) t # 使用到mybatis里面 ‘<’ 要进行转义 <
WHERE numberbvalue > 0- @days # ‘>’ 要进行转义 >
#如果要查询时候可以使用左连接。
此方式存在一定缺陷 如果是十二月份 会出现三十一天