sql陈列查询连续不断的两个时间，或者两个日期之间的列表

最新推荐文章于 2024-01-10 18:22:09 发布

置顶轻点别打脸

最新推荐文章于 2024-01-10 18:22:09 发布

阅读量1.5k

点赞数

分类专栏： Mysql 文章标签： mysql mysql查询连续时间 sql

本文链接：https://blog.csdn.net/loney_wolf/article/details/103165427

版权

Mysql 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

报表统计时，一张表中的数据往往存在时间间断的情况，报表呈现的效果需要连续不断的，这种情况可以在代码层对缺失的时间数据进行补充。
也可以在sql中查询出连续不断的时间，作为虚拟主表，联表查询报表数据

记录一下sql处理的情况

1. 查询连续不断的小时时间列表

这里是利用储存过程中执行while函数，将0时到当前小时的时间，拼接成连续字符串，然后借用mysql默认的mysql.help_topic表，将字符串以列的形式进行拆分展现

-- 储存过程存在则删除
DROP PROCEDURE IF EXISTS test_while_001;
-- 获取当前最大小时值（24小时制） 
-- SELECT FROM_UNIXTIME( UNIX_TIMESTAMP(), '%k' ); %k %H均表示24小时制小时
-- 当前时间
SET @maxHours = ( SELECT date_format( NOW( ), '%H' ) );
SET @hours = '0';

-- 定义函数，创建储存过程
DELIMITER $$
CREATE PROCEDURE test_while_001 ( ) BEGIN
	DECLARE
		i INT DEFAULT 0;
	WHILE
			i < @maxHours DO
			SET i = i + 1;
			SET @hours := CONCAT(@hours, "," ,i);
	END WHILE;
	
		/*** 此sql陈列小时列表，亦在此写入业务sql***/
		-- 借用mysql中默认表，help_topic将小时字符串，拆分成列表记录
		select substring_index(substring_index( @hours,',',help_topic_id+1),',',-1) as hours
		from mysql.help_topic
		--  此处left join 业务表，即可接入业务
		where help_topic_id<(length( @hours)-length(replace( @hours,',',''))+1);
		//sql结束忘了分号结束，不然sql报错
END $$
DELIMITER;
CALL test_while_001 ( );

以上是以储存过程结合help_topic实现，以下是单独利用mysql的help_topic表，将时间以入参形式传入，效果与上面一样

		--  SET @date =  "1,2,3,4,....";
	   example: #{时间字符串组} = "1,2,3,4,....";

		select substring_index(substring_index( #{时间字符串组},',',help_topic_id+1),',',-1) as businessAt
		from mysql.help_topic
		--  left join 业务表，即可接入业务查询
		where help_topic_id<(length(  #{时间字符串组})-length(replace( #{时间字符串组},',',''))+1)

以下是sql执行的结果
在这里插入图片描述

- 2.sql查询连续不断的日期列表

使用储存过程+help_topic实现

-- 储存过程存在则删除
DROP PROCEDURE IF EXISTS test_while_001;
-- 昨日日期
SET @endDate = (select DATE_SUB(curdate(),INTERVAL 1 DAY)) ;

SET @startDate = (select DATE_SUB(curdate(),INTERVAL 10 DAY)) ;

-- SET @startDate = (SELECT FROM_UNIXTIME(MIN(business_at),"%Y-%m-%d")  FROM ms_score_flow);

SET @dateDistance = (SELECT DATEDIFF(@endDate,@startDate));
SET @varDate=@endDate;

-- 定义函数，创建储存过程
DELIMITER $$
CREATE PROCEDURE test_while_001 ( ) BEGIN
	DECLARE
		i INT DEFAULT 1;
	WHILE
			i < @dateDistance DO
			SET i = i + 1;
			SET @varDate := CONCAT(@varDate, "," ,(select DATE_SUB(curdate(),INTERVAL i DAY)));
	END WHILE;
	
		/*** 此sql陈列日期列表，亦在此写入业务sql ***/
		-- 借用mysql中默认表，help_topic将小时字符串，拆分成列表记录
		select substring_index(substring_index( @varDate,',',help_topic_id+1),',',-1) as hours
		from mysql.help_topic
		-- left join 业务表接入业务数据
		where help_topic_id<(length( @varDate)-length(replace( @varDate,',',''))+1);
	
END $$
DELIMITER;
CALL test_while_001 ( );

以下sql是基于参考博客改动：https://blog.csdn.net/longvs/article/details/22745433

	-- 昨天的日期
SET @MAX_DATE = DATE_SUB( curdate( ), INTERVAL 1 DAY );

-- 业务表中最早的日期
-- SET @MIN_DATE = ( SELECT FROM_UNIXTIME( MIN( created_at ), '%Y-%m-%d' ) FROM 业务 TABLE );
SET @MAX_DATE = DATE_SUB( curdate( ), INTERVAL 7 DAY );

SET @i = - 1;
SET @SQL =
REPEAT
		( " select 1 union all",- datediff( @MIN_DATE, @MAX_DATE ) + 1 );
	
	SET @SQL = LEFT ( @SQL, length( @SQL ) - length( " union all" ) );
	
	-- --业务结合部分  开始----
	SET @SQL = concat( "select date_add(@MIN_DATE,interval @i:=@i+1 day) as businessAt from (", @SQL, ") as tmp" );
	-- --业务结合部分-结束----
	PREPARE stmt 
	FROM
		@SQL;
	EXECUTE stmt 
	
	-- ------不计入上面的sql，替换业务结合部分，即可结合业务查询----------------------
	SET @SQL = concat( "SELECT aws.businessAt,mafd.business_at FROM (
		-- 查询出最大日期与最小日期，之间的全部日期
		select date_add(@MIN_DATE,interval @i:=@i+1 day) as businessAt from (", @SQL, ") as tmp
		) aws
	LEFT JOIN ms_analysis_fans_day mafd ON mafd.business_at = aws.businessAt WHERE mafd.business_at IS NULL" );
	PREPARE stmt 
FROM
	@SQL;