Mysql 案例 - 统计特定时间内的字段数

Question

我正在尝试设置一个包含以下字段的报告:

非常需要报告来显示日期、该日期内的总记录(因此我按日期分组)，然后按小时计算 12 小时工作日(从上午 8 点到晚上 8 点)我需要计算记录在这些时间内出现的时间。经过一番头脑 Storm 和头脑 Storm 后，我想为什么不使用一个案例。这是我尝试过的:

 SELECT 
        DATE_FORMAT(signintime, '%b %d, %Y') Date, 
        COUNT(session_id) as Total,
        SUM(CASE WHEN HOUR(signintime) > 08 AND HOUR(signintime) < 09 THEN 1 ELSE 0 END) '8AM-9PM',
        SUM(CASE WHEN HOUR(signintime) > 09 AND HOUR(signintime) < 10 THEN 1 ELSE 0 END) '9AM-10AM',
        SUM(CASE WHEN HOUR(signintime) > 10 AND HOUR(signintime) < 11 THEN 1 ELSE 0 END) '10AM-11AM',
        SUM(CASE WHEN HOUR(signintime) > 11 AND HOUR(signintime) < 12 THEN 1 ELSE 0 END) '11AM-12PM',
        SUM(CASE WHEN HOUR(signintime) > 12 AND HOUR(signintime) < 13 THEN 1 ELSE 0 END) '12PM-1PM',
        SUM(CASE WHEN HOUR(signintime) > 13 AND HOUR(signintime) < 14 THEN 1 ELSE 0 END) '1PM-2PM',
        SUM(CASE WHEN HOUR(signintime) > 14 AND HOUR(signintime) < 15 THEN 1 ELSE 0 END) '2PM-3PM',
        SUM(CASE WHEN HOUR(signintime) > 15 AND HOUR(signintime) < 16 THEN 1 ELSE 0 END) '3PM-4PM',
        SUM(CASE WHEN HOUR(signintime) > 16 AND HOUR(signintime) < 17 THEN 1 ELSE 0 END) '4PM-5PM',
        SUM(CASE WHEN HOUR(signintime) > 17 AND HOUR(signintime) < 18 THEN 1 ELSE 0 END) '5PM-6PM',
        SUM(CASE WHEN HOUR(signintime) > 18 AND HOUR(signintime) < 19 THEN 1 ELSE 0 END) '6PM-7PM',
        SUM(CASE WHEN HOUR(signintime) > 19 AND HOUR(signintime) < 20 THEN 1 ELSE 0 END) '7PM-8PM'
 FROM session 
 WHERE session.status = '3'
 GROUP by HOUR(signintime), Date;

如果您注意到上面的图片，那一整天(2013 年 4 月 19 日)的总数为 1，现在如果您注意到时间(上午 8 点 - 晚上 8 点)，它们都已清零。我不确定该去哪里/在哪里调试它。希望在另一双眼睛的帮助下我能插入这一切。

问候。

Answer 1

您不需要同时使用 >和<要获得结果，您只需检查 hour值:

select DATE_FORMAT(signintime, '%b %d, %Y') Date, 
  count(session_id) as Total,
  SUM(CASE WHEN HOUR(signintime) = 08 THEN 1 ELSE 0 END) '8AM-9AM',
  SUM(CASE WHEN HOUR(signintime) = 09 THEN 1 ELSE 0 END) '9AM-10AM',
  SUM(CASE WHEN HOUR(signintime) = 10 THEN 1 ELSE 0 END) '10AM-11AM',
  SUM(CASE WHEN HOUR(signintime) = 11 THEN 1 ELSE 0 END) '11AM-12PM',
  SUM(CASE WHEN HOUR(signintime) = 12 THEN 1 ELSE 0 END) '12PM-1PM',
  SUM(CASE WHEN HOUR(signintime) = 13 THEN 1 ELSE 0 END) '1PM-2PM',
  SUM(CASE WHEN HOUR(signintime) = 14 THEN 1 ELSE 0 END) '2PM-3PM',
  SUM(CASE WHEN HOUR(signintime) = 15 THEN 1 ELSE 0 END) '3PM-4PM',
  SUM(CASE WHEN HOUR(signintime) = 16 THEN 1 ELSE 0 END) '4PM-5PM',
  SUM(CASE WHEN HOUR(signintime) = 17 THEN 1 ELSE 0 END) '5PM-6PM',
  SUM(CASE WHEN HOUR(signintime) = 18 THEN 1 ELSE 0 END) '6PM-7PM',
  SUM(CASE WHEN HOUR(signintime) = 19 THEN 1 ELSE 0 END) '7PM-8PM'
from session
WHERE session.status = '3'
group by DATE_FORMAT(signintime, '%b %d, %Y');

参见SQL Fiddle with Demo