mysql 统计本日数据每两小时间隔

网上也看了很多没有找到合适的，最终自己找了笨办法；有错误望指正

select 
sum(case when t.hours >=0 and t.hours <2 then 1 else 0 end) as '0:00-2:00',
sum(case when t.hours >=2 and t.hours <4 then 1 else 0 end) as '2:00-4:00',
sum(case when t.hours >=4 and t.hours <6 then 1 else 0 end) as '4:00-6:00',
sum(case when t.hours >=6 and t.hours <8 then 1 else 0 end) as '6:00-8:00',
sum(case when t.hours >=8 and t.hours <10 then 1 else 0 end) as '8:00-10:00',
sum(case when t.hours >=10 and t.hours <12 then 1 else 0 end) as '10:00-12:00',
sum(case when t.hours >=12 and t.hours <14 then 1 else 0 end) as '12:00-14:00',
sum(case when t.hours >=14 and t.hours <16 then 1 else 0 end) as '14:00-16:00',
sum(case when t.hours >=16 and t.hours <18 then 1 else 0 end) as '16:00-18:00',
sum(case when t.hours >=18 and t.hours <20 then 1 else 0 end) as '18:00-20:00',
sum(case when t.hours >=20 and t.hours <22 then 1 else 0 end) as '20:00-22:00',
sum(case when t.hours >=22 then 1 else 0 end) as '22:00-0:00'
from (
select hour(tmo.create_time) hours from tm_order tmo where date(tmo.create_time) = date('2020-08-26')
) t

结果如下

xml中配置如下
返回类型是LinkedHashMap 否则影响排序

<select id="thisDay" resultType="java.util.LinkedHashMap" parameterType="String">
</select>

mapper 接口中

public Map<String,Object> thisDay(String today);

使用的时候循环Map 组装自己需要的结构就行

for(Map.Entry<String,Object> entry : selectResult.entrySet()) {
    listKey.add(entry.getKey());
    listValue.add(entry.getValue().toString());
}