从当前时间戳生成MySQL每小时​​细分

本文介绍了从当前时间戳生成MySQL每小时​​细分的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

恐怕这可能是一个非常尴尬的简单问题-但此刻我的想法完全陷入僵局.

I'm afraid this is probably a very embarrassingly easy question - but my mind is just completely stuck at this hour.

我有一个表格，其中存储了不同人进行的活动的数量及其发生的时间.

I have a table that stores the number of activities carried out by different people, and the time it took place in.

我想创建一个接受该人姓名作为参数的报告，并显示从当前时间戳(now())开始的该人在过去24小时内每小时的活动次数.

I want to create a report that accepts the person's name as a parameter, and show the number of activities per hour for that person during each of the previous 24 hours starting from current timestamp (now()).

现在

SELECT hour(TimeStamp), activities FROM tbl1 WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 24 HOUR) AND Name = ? GROUP BY hour(timestamp)

仅在存在任何活动的那几个小时返回给我.但是，我希望在没有活动的情况下进行24小时的完整细分(零).

only returns to me those hours when any activity was present. However, I want a complete 24 hour breakdown with zero for when there was no activity.

即我要

Hour | No. of Activities 1 | 34 4 | 22 9 | 86

但我想要

Hour | No. of Activities 1 | 34 2 | 0 3 | 0 4 | 22 5 | 0 ... etc.

我该怎么做?

(示例中的小时顺序无关紧要)

(The order of hours in the example is irrelevant)

推荐答案

您可以创建hourly表并执行LEFT JOIN

create table hourly ( /* hour is not a reserved keyword */ hour smallint(2) not null default 0 ); insert into hourly values (0),(1).... until 24 SELECT hourly.hour, COALESCE(COUNT(activities),0) AS "No of Activities" FROM hourly LEFT JOIN tbl1 ON hourly.hour=hour(tbl1.TimeStamp) WHERE tbl1.timestamp>=DATE_SUB(NOW(), INTERVAL 24 HOUR) AND tbl1.Name=? GROUP BY hourly.hour ORDER BY hourly.hour;