我有一个带有电话的
MySQL表.每行意味着一个电话.
专栏是：
专栏是：
start_time start_date duration
我需要同时拨打最多的电话.这是因为电话交换尺寸.
我的解决方案是创建两个时间戳列timestamp_start和timestamp_end.然后我每天一秒钟地运行一个循环,并向MysqL询问：
SELECT Count(*) FROM tbl WHERE start_date IN (thisday,secondday) AND "this_second_checking" BETWEEN timestamp_start AND timestamp_end;
这很慢.
有更好的解决方案吗？谢谢！
编辑 – 我使用这个解决方案,它给了我正确的结果.有使用sql层dibi – http://dibiphp.com/cs/quick-start.
$starts = dibi::query("SELECT ts_start,ts_end FROM " . $tblname . " GROUP BY ts_start");
if(count($starts) > 0):
foreach ($starts as $row) {
if(isset($result)) unset($result);
$result = dibi::query('SELECT Count(*) FROM ' . $tblname . ' WHERE "'.$row->ts_start.'" BETWEEN ts_start AND ts_end');
$num = $result->fetchSingle();
if($total_max < $num):
$total_max = $num;
endif;
}
endif;
echo "Total MAX: " . $total_max;
您应该为每一行(电话)查看当时其他电话是否处于活动状态,而不是逐秒运行.之后,您按行的ID对所有结果进行分组,并检查哪个具有最大计数.所以基本上是这样的：
SELECT MAX(calls.count)
FROM (
SELECT a.id,COUNT(*) AS count
FROM tbl AS a
INNER JOIN tbl AS b ON (
(b.timestamp_start BETWEEN a.timestamp_start AND a.timestamp_end)
OR
(b.timestamp_end BETWEEN a.timestamp_start AND a.timestamp_end)
)
GROUP BY a.id
) AS calls
在timestamp列上创建索引也会有所帮助.
