php - 使用 php 或 mysql 显示 0 到 23 小时表

Question

我在mysql中有一个表，其中有时间日期和军事格式(2014-01-22 16:10:02)

来电者数据

|ID | caller_id   |       call_time      
| 1 | 123         | 2014-01-22 16:10:02          
| 2 | 123         | 2014-01-22 16:30:02          
| 3 | 123         | 2014-01-22 17:10:02          
| 4 | 123         | 2014-01-22 18:05:45         

他们的时间范围如 16:10 和 16:30 。我使用 HOUR() 对它们进行分组，结果是 16。我会计算调用者在该小时范围内调用了多少次。所以那就是 2。我的查询:

SELECT caller_id,
HOUR(str_to_date(date_stamp, "%Y-%m-%d %H:%i:%s")) as hourly,
count(*) as total
from caller_data
group by hourly
order by hourly;

结果应该是:

caller_id    |  hourly |   total  
 123         |   16    |    2   
 123         |   17    |    1 
 123         |   18    |    1

我想在 0-23 上显示这 3 行。我一直在尝试用 php 显示它，但当我为 $i <= 23 ..

创建一个时，它显示了 3 次

for($i=0;i<=23;$i++)
{
   foreach ($rec as $row):
       echo $row['hourly'];
       echo $row['caller_id'];
       echo $row['total'];
   endforeach;
}

我不知道是否可以在 mysql 上显示这个。有人可以展示如何使用 php 或 mysql 来做到这一点吗？

| hours | caller_id   | total 
| 0     | 123         |  0
| 1     | 123         |  0        
| 2     | 123         |  0       
| 3     | 123         |  0     
| 4     | 123         |  0
| 5     | 123         |  0
| 6     | 123         |  0        
| 7     | 123         |  0       
| 8     | 123         |  0     
| 9     | 123         |  0
| 10    | 123         |  0
| 11    | 123         |  0
| 12    | 123         |  0        
| 13    | 123         |  0       
| 14    | 123         |  0     
| 15    | 123         |  0
| 16    | 123         |  2        
| 17    | 123         |  1       
| 18    | 123         |  1     
| 19    | 123         |  0
| 20    | 123         |  0
| 21    | 123         |  0     
| 22    | 123         |  0
| 23    | 123         |  0

Answer 1

您可以在 SQL 中使用左外连接来完成此操作。您只需输入所有时间:

select caller_id, n.n as hourly, coalesce(total, 0) as total
from (select 1 as n union all select 2 union all select 3 union all select 4 union all
      select 5 union all select 6 union all select 7 union all select 8 union all
      select 9 union all select 10 union all select 11 union all select 12 union all
      select 13 union all select 14 union all select 15 union all select 16 union all
      select 17 union all select 18 union all select 19 union all select 20 union all
      select 21 union all select 22 union all select 23 union all select 24
     ) n left outer join
     (select caller_id, HOUR(str_to_date(date_stamp, "%Y-%m-%d %H:%i:%s")) as hourly,
             count(*) as total
      from caller_data
      group by hourly
     ) rep
     on n.n = rep.hourly
order by hourly;