PHP MySQL左连接统计小时新增交易数结果异常求助

问题根源 #

你的查询中，DATE(edited) = date_generator.date这个关联条件只对比了日期部分，完全忽略了小时信息。当天所有status='NEW'的交易都会和时间生成器里的2022-12-29 00:00:00这一行匹配，导致其他小时的统计结果都是0，只有0点行统计了全部11条数据。

修复后的SQL #

SELECT
   date_generator.date as the_date,
   IFNULL(COUNT(transactions.edited), 0) as count
from (
   select DATE_ADD('2022-12-29 00:00:00', INTERVAL (@i:=@i+1)-1 HOUR) as `date`
   from information_schema.columns,(SELECT @i:=0) gen_sub 
   where DATE_ADD('2022-12-29 00:00:00',INTERVAL @i HOUR) BETWEEN '2022-12-29 00:00:00' AND '2022-12-29 23:59:59'
) date_generator
left join `transactions` 
  on transactions.edited >= date_generator.date
  AND transactions.edited < DATE_ADD(date_generator.date, INTERVAL 1 HOUR)
  AND status = 'NEW'
GROUP BY date_generator.date

关键修改说明 #

1. 把原关联条件DATE(edited) = date_generator.date替换为区间匹配：
   • transactions.edited >= date_generator.date：交易时间不小于当前小时的起始时间
   • transactions.edited < DATE_ADD(date_generator.date, INTERVAL 1 HOUR)：交易时间小于下一个小时的起始时间
     这种方式能精准把交易归入对应的小时桶，同时如果edited字段有索引，不会因为函数转换导致索引失效，查询效率更高。
2. 明确GROUP BY的字段为date_generator.date，避免别名歧义。

验证效果 #

修复后，查询会将交易按实际发生小时拆分统计，得到你期望的输出：09点统计1条，10点统计10条，其余小时为0。

内容的提问来源于stack exchange，提问作者Speego