问题描述
可能的重复:
如何在 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
这篇关于即使没有记录,也要每小时行一次的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!