可能的重复:如何在 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更多推荐
即使没有记录,也要每小时行一次
发布评论