我现在有一个查询,它显示了这段时间之间的所有调用,有没有办法将其格式化为在一个查询中显示"day(9-5)"one_answers"night(5-12)"?
select dst, count(dst) as Day from cdr
WHERE lastapp='Queue'
AND Date(calldate) = '2012-03-23'
AND TIME(calldate) BETWEEN '08:00' AND '17:00'
GROUP BY dst;
+------+-----+
| dst | Day |
+------+-----+
| 1010 | 10 |
| 1011 | 21 |
| 1012 | 7 |
+------+-----+
理想情况下,我想要的是这样,这样我就可以在PHP的数组中获取它,而不必执行2个查询。
+------+-----+-------+
| dst | Day | Night |
+------+-----+-------+
| 1010 | 10 | 20 |
| 1011 | 21 | 12 |
| 1012 | 7 | 4 |
+------+-----+-------+
是;你可以写这样的东西:
SELECT dst,
COUNT(IF(TIME(calldate) BETWEEN '08:00' AND '17:00', 1, NULL)) AS Day,
COUNT(IF(TIME(calldate) > '17:00', 1, NULL)) AS Night
FROM cdr
WHERE lastapp = 'Queue'
AND DATE(calldate) = '2012-03-23'
GROUP
BY dst
;
(COUNT(...)
统计...
为非空的行数。IF(boolean, value_if_true, value_if_false)
按其发音执行—请参阅http://dev.mysql.com/doc/refman/5.6/en/control-flow-functions.html#function_if—尽管我应该提到它是MySQL特定的函数。如果你没有特别将你的问题标记为MySQL,我会建议使用CASE WHEN boolean THEN value_if_true ELSE value_if_false END
,因为这是大多数或所有DMBS都支持的标准语法。)