如何统计不同时间段的数量？

数据库存放数据如下： SELECT t.start_time,t.end_time FROM vipsoft_order t MySQL SELECT CONCAT(LPAD(hour_range, 2, '0'), ':00~', LPAD(hour_range + 1, 2, '0'), ':00') as time_period, SUM(CASE WHEN HOUR(start_time) = hour_range THEN 1 ELSE 0 END) as start_count, SUM(CASE WHEN HOUR(end_time) = hour_range THEN 1 ELSE 0 END) as end_count FROM vipsoft_order, (SELECT 0 as hour_range UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 UNION SELECT 21 UNION SELECT 22 UNION SELECT 23) hours GROUP BY hour_range ORDER BY hour_range; PostgreSQL版本 SELECT TO_CHAR(hour_range, '00') || ':00~' || TO_CHAR(hour_range + 1, '00') || ':00' as time_period, COUNT(CASE WHEN EXTRACT(HOUR FROM start_time) = hour_range THEN 1 END) as start_count, COUNT(CASE WHEN EXTRACT(HOUR FROM end_time) = hour_range THEN 1 END) as end_count FROM generate_series(0, 23) as hour_range LEFT JOIN custody_order ON EXTRACT(HOUR FROM start_time) = hour_range OR EXTRACT(HOUR FROM end_time) = hour_range GROUP BY hour_range ORDER BY hour_range; 关键点说明： 小时范围生成：创建0-23的小时序列 时间格式化：将小时格式化为08:00~09:00的格式 计数逻辑： start_count：统计start_time小时等于当前时段的记录 end_count：统计end_time小时等于当前时段的记录 处理NULL值：使用LEFT JOIN确保即使某个时段没有数据也会显示0 这样您就能得到按小时分组的开始和结束数量统计了。 Mybatis 不建实体类，将数据反给前端: https://www.cnblogs.com/vipsoft/p/19195490