mysql 一天24小时时段, 用于左连接按小时分组数据后,某个小时短无数据,需要补0的情况

yaDong Huang

已于 2022-03-10 14:26:46 修改

阅读量1.4k

点赞数

文章标签： mysql 数据库 database

于 2022-03-10 14:19:53 首次发布

本文链接：https://blog.csdn.net/hyd19931002/article/details/123400389

版权

select LPAD(0,2,0) as sj
union all select LPAD(1,2,0)
union all select LPAD(2,2,0)
union all select LPAD(3,2,0)
union all select LPAD(4,2,0)
union all select LPAD(5,2,0)
union all select LPAD(6,2,0)
union all select LPAD(7,2,0)
union all select LPAD(8,2,0)
union all select LPAD(9,2,0)
union all select 10
union all select 11
union all select 12
union all select 13
union all select 14
union all select 15
union all select 16
union all select 17
union all select 18
union all select 19
union all select 20
union all select 21
union all select 22
union all select 23
from dual

如图:

select LPAD(0,2,0) as sj
union all select LPAD(1,2,0)
union all select LPAD(2,2,0)
union all select LPAD(3,2,0)
union all select LPAD(4,2,0)
union all select LPAD(5,2,0)
union all select LPAD(6,2,0)
union all select LPAD(7,2,0)
union all select LPAD(8,2,0)
union all select LPAD(9,2,0)
union all select 10
union all select 11
union all select 12
union all select 13
union all select 14
union all select 15
union all select 16
union all select 17
union all select 18
union all select 19
union all select 20
union all select 21
union all select 22
union all select 23
from dual

<!-- 按小时计算 -->
<select id="getValGroupHour" resultType="map" parameterType="java.util.Map">
	select ta.sj sj, ifnull(tb.avg,0) avg from  (<include refid="day"></include>) ta
		left join  (
				SELECT
				DATE_FORMAT(bjsj,'%H') sj, COUNT(*)/#{interval,jdbcType=INTEGER} avg
				FROM b_jtsg_jjd jjd
					where bjsj > #{kssj,jdbcType=TIMESTAMP}
					and #{jssj,jdbcType=TIMESTAMP} > bjsj
				group by DATE_FORMAT(bjsj,'%H') 
		) tb on ta.sj = tb.sj
</select>