mySQL：在结果内的间隔之间自动插入行

Question

我的任务是提取按小时分组的数据。我通过发出类似于下面的命令来执行此操作：

SELECT DISTINCT 
   COUNT(player_id) AS `count`, 
   YEAR(date_created) AS `year`, 
   MONTH(date_created) AS `month`, 
   DAY(date_created) AS `day`, 
   HOUR(date_created) AS `hour` 

FROM `logs` 

WHERE 
   (controller='player') AND 
   (action='updatehash') AND (date_created >= FROM_UNIXTIME(1317916800)) AND 
   (date_created <= FROM_UNIXTIME(1318003199)) 

GROUP BY `year`, `month`, `day`, `hour` 

ORDER BY `year` ASC, `month` ASC, `day` ASC, `hour` ASC 

返回类似于下面的结果：

Array
(
    [0] => Array
        (
            [count] => 2
            [year] => 2011
            [month] => 10
            [day] => 7
            [hour] => 16
        )

    [1] => Array
        (
            [count] => 5
            [year] => 2011
            [month] => 10
            [day] => 7
            [hour] => 17
        )

    [2] => Array
        (
            [count] => 21
            [year] => 2011
            [month] => 10
            [day] => 7
            [hour] => 18
        )

    [3] => Array
        (
            [count] => 3
            [year] => 2011
            [month] => 10
            [day] => 7
            [hour] => 19
        )

)

结果似乎没问题，只是我必须让它返回过去 24 小时的结果，包括没有计数值的时间。因此，根据我上面发布的结果，它应该返回从 10 月 7 日 19:00 到 10 月 6 日 18:00 的结果。

这可能吗？

谢谢大家！ ;)

Answer 1

简短的回答，不使用这个SQL，你没有连接表，所以你不能使用“左连接”技术。因为你在这段时间里确实没有任何日志记录。

也许您可以尝试创建 FUNCTION 方法或使用您的编程语言每小时插入至少一条记录。