MySQL 自动补齐日期,查询时间段日期

原创已于 2022-07-28 19:34:16 修改 · 1.4k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #mybatis

于 2022-07-28 16:44:07 首次发布

Java 专栏收录该内容

3 篇文章

订阅专栏

该查询语句用于按天迭代日期，从'2022-07-01'开始，直到'2022-09-21'之前，展示日期格式化的结果。通过将原始多表联合查询改写为单次DATE_ADD操作，提高了效率并简化了逻辑。

SET @i :=- 1,@time := '2022-08-01 00:00:00';
SELECT  @time :=DATE_SUB( '2022-08-01 00:00:00', INTERVAL ( @i := @i + 1 ) DAY ), date_format( @time, '%Y-%m-%d' ) AS `time`
				FROM
					  (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 UNION SELECT 0) xc1, 
						(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 UNION SELECT 0) xc2,
						(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 UNION SELECT 0) xc3
						WHERE 
						@time > '2022-07-01 00:00:00';

查询数据库

改为一句语法

	SELECT
		@time := DATE_ADD( IFNULL( @time, '2022-07-28 19:19:57' ), INTERVAL 1 DAY ) AS mday,
		date_format( @time, '%Y-%m-%d' ) AS `time` , IF(@time < '2022-09-21 19:19:57' ,@time := null,@time)
	FROM
		(
		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 UNION
		SELECT
			0 
		) xc1,
		(
		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 UNION
		SELECT
			0 
		) xc2,
		(
		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 UNION
		SELECT
			0 
		) xc3 
	WHERE
		@time < '2022-09-21 19:19:57' or @time is null