按时间维度统计——该时间段没有的显示为0

在做统计功能时，按时间维度统计，按年、按月、按季度统计
如果数据库在该时间中没有产生数据，则显示为零。说白了就是时间要连续展示

• 按月

	select DATE_FORMAT(date_add('2016-01-20 00:00:00', interval row MONTH),'%Y-%m')as  date from
			 ( 
					SELECT @row := @row + 1 as row FROM 
					(select 0 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) t,
					(select 0 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) t2, 
					(SELECT @row:=-1) r
			 ) se
			 where DATE_FORMAT(date_add('2016-01-20 00:00:00', interval row MONTH),'%Y-%m') <= DATE_FORMAT('2021-04-02 00:00:00','%Y-%m')

• 按年

			  select DATE_FORMAT(date_add('2016-01-20 00:00:00', interval row YEAR),'%Y')as timedDimension from
			 ( 
					SELECT @row := @row + 1 as row FROM 
					(select 0 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) t,
					(select 0 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) t2, 
					(SELECT @row:=-1) r
			 ) se
			  where DATE_FORMAT('2021-04-02 00:00:00','%Y')>= DATE_FORMAT(date_add('2016-01-20 00:00:00', interval row YEAR),'%Y')

• 按季度

              select CONCAT(DATE_FORMAT(date_add('2016-01-20 00:00:00', interval row QUARTER),'%Y-Q')
                ,FLOOR((date_format(date_add('2016-01-20 00:00:00', interval row QUARTER),'%m')+2)/3)) as timedDimension from
			 ( 
					SELECT @row := @row + 1 as row FROM 
					(select 0 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) t,
					(select 0 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) t2, 
					(SELECT @row:=-1) r
			 ) se
	 where DATE_FORMAT('2021-04-02 00:00:00','%Y-%m')>= DATE_FORMAT(date_add('2016-01-20 00:00:00', interval row QUARTER),'%Y-%m')
```![在这里插入图片描述](https://img-blog.csdnimg.cn/20210618154043960.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg2MzQ2Mw==,size_16,color_FFFFFF,t_70)
季度展示可以根据产品的