php - 在编写查询时需要帮助

Question

我有一个用来保存电话记录的表。我需要在特定日期范围内(例如 3 月 1 日至 3 月 5 日)每小时(例如 8:00 至 9:00)接听和未接听电话计数，我不需要分隔日期，只需要分隔时间。

我已经使用了这个查询:

SELECT `event`, DATE(`time`) AS `date`, HOUR(`time`) AS `hour`, COUNT(*) AS `count` FROM `queue_log` WHERE `queuename` = 'Hozoori_Q' AND `event` IN ('CONNECT', 'RINGNOANSWER') AND `time` >= '2014-03-01' AND `time` <= '2014-03-05' GROUP BY `event`, `date`, `hour`;

“事件”字段是调用状态。

这个查询输出是这样的:

1 => 
array (size=4)
'event' => string 'CONNECT' (length=7)
'date' => string '2014-03-01' (length=10)
'hour' => string '9' (length=1)
'count' => string '99' (length=2)
2 => 
array (size=4)
'event' => string 'RINGNOANSWER' (length=7)
'date' => string '2014-03-01' (length=10)
'hour' => string '9' (length=1)
 'count' => string '5' (length=2)

但我想变成这样:

  1 => 
    array (size=4)
      'date' => string '2014-03-01' (length=10)
      'hour' => string '9' (length=1)
      'answered_count' => string '99' (length=2)
      'not_answered_count' => string '5' (length=2)

有什么解决办法吗？

Answer 1

直接的 SQL 答案:

SELECT DATE(`time`) AS `date`, 
       HOUR(`time`) AS `hour`, 
       SUM(`event` = 'CONNECT') AS `answered_count`, 
       SUM(`event` = 'RINGNOANSWER') AS `not_answered_count` 
  FROM `queue_log` 
 WHERE `queuename` = 'Hozoori_Q' 
   AND `event` IN ('CONNECT', 'RINGNOANSWER') 
   AND `time` >= '2014-03-01' 
   AND `time` <= '2014-03-05'
 GROUP BY `date`, `hour`;