即使没有记录，也要每小时行一次

本文介绍了即使没有记录，也要每小时行一次的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

可能的重复:如何在 mysql 中制作行生成器mysql 中的generate_series 等价物

我在创建 mysql 查询时遇到了麻烦.

I've got a trouble with creating mysql Query.

我的 PHP 脚本在每次运行时执行此查询:INSERT INTO 执行 SET UserIp='%s'(%s 是用户 IP)

My PHP script executes this query on each run: INSERT INTO Executes SET UserIp='%s' (%s is user IP)

执行表是:

ExecuteId UNSIGNED BIGINT AI PRIMARY Date TIMESTAMP DEFAULT CURRENT_TIMESTAMP INDEX UserIp CHAR(24) ... | Some Columns

我想检索每小时的执行次数.最明显的解决方案是:

I want to retrive number of Executes in each hour. The most obvious solution would be:

SELECT COUNT(*) as ExecutesNum, DATE(Date) as D, HOUR(Date) as H GROUP BY D, H

它可以工作，但它不会在没有执行的几个小时内创建行.

And it works, BUT it does not create rows for hours where there were no executes.

我应该修改什么才能得到如下结果:

What should I modify to get result like:

1 | 2012-09-01 | 14 **0 | 2012-09-01 | 15** 11 | 2012-09-01 | 16 1 | 2012-09-01 | 17

推荐答案

这是一个比较常见的问题，我通常通过创建一个包含所有时间的临时表来解决，如下所示:

This is a rather common problem, which I usually solve by creating a temporary table containing all the hours, like this:

DROP TABLE IF EXISTS hours; CREATE TABLE hours (hour VARCHAR(13) PRIMARY KEY); DROP PROCEDURE IF EXISTS fill_hours; DELIMITER | CREATE PROCEDURE fill_hours(start_time DATETIME, end_time DATETIME) BEGIN DECLARE crt_time DATETIME; SET crt_time=DATE_SUB(start_time, INTERVAL DATE_FORMAT(start_time, '%i:%s') MINUTE_SECOND); WHILE crt_time < end_time DO INSERT INTO hours VALUES(DATE_FORMAT(crt_time, '%Y-%m-%d-%H')); SET crt_time = DATE_ADD(crt_time, INTERVAL 1 HOUR); END WHILE; END | CALL fill_hours( (SELECT MIN(Date) FROM Executes), (SELECT MAX(Date) FROM Executes) );

然后您可以将此表与原始表连接以获得您想要的:

You can then join this table to the original one to get what you want:

SELECT h.hour, COUNT(e.ExecuteId) FROM hours h LEFT JOIN Executes e ON DATE_FORMAT(e.Date, "%Y-%m-%d-%H") = h.hour GROUP BY h.hour