MySQL 统计每个时间段的数量

数据库存放数据如下：

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 9UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19UNION 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;

关键点说明：

1. 小时范围生成：创建0-23的小时序列
2. 时间格式化：将小时格式化为08:00~09:00的格式
3. 计数逻辑：
   • start_count：统计start_time小时等于当前时段的记录
   • end_count：统计end_time小时等于当前时段的记录
4. 处理NULL值：使用LEFT JOIN确保即使某个时段没有数据也会显示0

这样您就能得到按小时分组的开始和结束数量统计了。