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

TaLinBoy

已于 2022-07-28 19:34:16 修改

阅读量1.2k

点赞数

分类专栏： Java 文章标签： mysql 数据库 mybatis

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

本文链接：https://blog.csdn.net/TaLinBoy/article/details/126038306

版权

Java 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

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