即使没有记录，也要每小时行一次 [英] Row for each hour even if there is no record

问题描述

可能的重复:
如何在 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

这篇关于即使没有记录，也要每小时行一次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！