[大数据]SQL查询数据给前端做报表显示日期不间断

最近在做数据统计,返回给前端,数据的日期还不能断,简短的做个记录

我把sql分成两个部分来做,相当于是两个结果集左关联

第一部分先做一张日期不断的时间表(月份表,年份表)

select date_add('2022-03-20',interval @i:=@i+1 day) as date from 
(
		select 1
        union all select 1
	    union all select 1
		union all select 1
		union all select 1
	    union all select 1
		union all select 1
		union all select 1
		union all select 1
		union all select 1
		union all select 1
		union all select 1
		union all select 1
		union all select 1
		union all select 1
		union all select 1
) as tmp,(select @i:= -1) t

date_add('2022-03-20',interval @i:=@i+1 day)代表在这个日期基础上加1天

(@i:=@i+1)代表定义一个变量，每次叠加1；

(SELECT @i:=-1) AS t代表建立一个临时表，t是随便取的表名，但别名一定要的。

为什么等于-1因为要算上2022-03-20这一天所以是从0开始加的.

UNION ALL 命令会列出所有的值。效果如下

连续的时间有了,那就是需要查询你想要的结果的SQL了

我需要的结果集如下

SELECT d.days,	
		sum(d.user_count) AS userCount,
        sum(d.charge_count) AS chargeCount,
        sum(d.charge_money_count) AS chargeMoneyCount,
        sum(d.power_count) AS powerCount,
        sum(d.energy_value) AS energyValue	
from area_info a
		LEFT JOIN area_station_bind b on a.id=b.area_id
		LEFT JOIN station_info c on c.id = b.station_id
		LEFT JOIN station_use_statistics d on c.id=d.station_id
		GROUP BY d.days

?这是根据日期分组,然后求字段数据的总和

两个结果集有了之后,就需要把他们进行关联结果如下

select d.date as `date` ,c.days,
    c.userCount,
    c.chargeCount,
    c.chargeMoneyCount,
    c.powerCount,
    c.energyValue 
from
(
    select date_add('2022-03-20',interval @i:=@i+1 day) as date 
        from 
		(
			select 1
            union all select 1
			union all select 1
			union all select 1
			union all select 1
			union all select 1
			union all select 1
			union all select 1
			union all select 1
			union all select 1
			union all select 1
			union all select 1
			union all select 1
			union all select 1
			union all select 1
			union all select 1
        ) as tmp,(select @i:= -1) t
) as d
left join
(
    SELECT d.days,	
	sum(d.user_count) AS userCount,
    sum(d.charge_count) AS chargeCount,
    sum(d.charge_money_count) AS chargeMoneyCount,
    sum(d.power_count) AS powerCount,
    sum(d.energy_value) AS energyValue	
	from area_info a
	    LEFT JOIN area_station_bind b on a.id=b.area_id
	    LEFT JOIN station_info c on c.id = b.station_id
		LEFT JOIN station_use_statistics d on c.id=d.station_id
		GROUP BY d.days
) as c

on d.date = DATE_FORMAT(c.days,'%Y-%m-%d')
group by d.date

on d.date = DATE_FORMAT(c.days,'%Y-%m-%d')

把这两个结果集的日期形成关联

然后要根据第一个结果集的日期进行排序这样才能够形成不断的日期表

?这是在mysql里的结果,放到项目里有几个需要解决的,就是我怎么知道要UNION ALL几列

这个可以通过计算参数的开始日期和结束日期来获取foreach的次数

select f.date as `date` ,
       c.days,
       c.userCount,
       c.chargeCount,
       c.chargeMoneyCount,
       c.powerCount,
       c.energyValue 
from
(
    select date_add(#{date1},interval @i:=@i+1 day) as date
    from 
    (
        select 1
        <foreach item="index" collection="countArr">
            union all select 1
        </foreach>
        ) as tmp,(select @i:= -1) t
    ) as f
        left join
        (
        SELECT d.days,
        sum(d.user_count) AS userCount,
        sum(d.charge_count) AS chargeCount,
        sum(d.charge_money_count) AS chargeMoneyCount,
        sum(d.power_count) AS powerCount,
        sum(d.energy_value) AS energyValue
        from area_info a
        LEFT JOIN area_station_bind b on a.id=b.area_id
        LEFT JOIN station_info c on c.id = b.station_id
        LEFT JOIN station_use_statistics d on c.id=d.station_id
        where c.dept_id = #{dept}
        <if test="stationInfoId != null">
            AND b.station_id=#{stationInfoId}
        </if>
        <if test="areaId != null">
            AND a.id=#{areaId}
        </if>

        GROUP BY d.days
        ) as c

        on f.date = DATE_FORMAT(c.days,'%Y-%m-%d'
)

     group by f.date

这是完整的过程

顺带发现了一个SQL的函数时计算两个日期之间相差多少个月

				SELECT TIMESTAMPDIFF(MONTH, '2022-03-20','2023-03-20');

但是会有一个问题就是月份相差时间会根据天数不同四舍五入

可能这个写的很复杂,希望各位大神给与指正

有更好的方法希望能留言给我,非常感谢