报表统计时，一张表中的数据往往存在时间间断的情况，报表呈现的效果需要连续不断的，这种情况可以在代码层对缺失的时间数据进行补充。
也可以在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执行的结果

sql server 查询两个时间段的数据 sql查两个时间内的数据_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是基于参考改动
-- 昨天的日期
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;
  • 这种写法也可以将查询出的日期列表作为虚拟主表，但是对于加入查询条件有点不友好（个人能力有限没能完美使用） 以下是查询结果

sql server 查询两个时间段的数据 sql查两个时间内的数据_mysql_02