当前位置：Gxlcms >
数据库问题 >
mysql 根据当前日期，查询24个小时每小时的数量，为null填充0
mysql 根据当前日期，查询24个小时每小时的数量，为null填充0
时间：2021-07-01 10:21:17
帮助过：4人阅读
CREATEDATE as YYmmdd,
CONCAT(CREATEDATE,‘ ‘,d.i) YYmmddHH,
d.i as HH,
case when zscum
is null then 0 else zscum
end as zscum
from
(
select
DATE_FORMAT(CREATEDATE,‘%Y-%m-%d‘)
as CREATEDATE,
SUM(
CASE WHEN e.status
= ‘1‘ THEN 1 ELSE 0 END )
AS zscum,
HOUR ( CREATEDATE ) as hour
from event_base_info e
-- 自己的条件
where e.status
=‘1‘ and e.STATE
not in(
‘3702820001‘)
-- 时间
and DATE_FORMAT( CREATEDATE,
‘%Y-%m-%d‘ )
like ‘2019-06-04%‘
GROUP BY HOUR ( CREATEDATE )
) o
-- 中间表
RIGHT JOIN (
select i
from num
where i
between ‘0‘ and ‘24‘ ) d
on o.hour
= d.i
GROUP BY d.i
5、效果 （HH是小时，用了中间表）
如有疏漏，请指正！谢谢
mysql 根据当前日期，查询24个小时每小时的数量，为null填充0
标签：nbsp select mysql sql eve for 类型 concat create
