时间戳

首页 > 解决方案 > 从 MySQL 获取每小时数据

mysql - 从 MySQL 获取每小时数据

问题描述

下面的查询检索 Date Hourly 并将数据分组为每小时格式

SELECT Hour(created)   AS hour, created,image
    FROM   user_screenshot
    WHERE  created BETWEEN ( Curdate() + INTERVAL (SELECT Hour(Now())) hour - 
                              INTERVAL 23 hour ) AND Now() AND date(created) = '2021-01-27'
    GROUP  BY hour 
    ORDER  BY ( Curdate() + INTERVAL (SELECT Hour(Now())) hour - INTERVAL 23 hour )

它以这种方式打印数据在此处输入图像描述

没关系。但是是否有可能检索属于那个小时的所有记录。对于上面的示例，查询表示每小时有447记录，每小时21有1结果22。是否有可能在一个查询中获取数据库中存在的那些结果以及这些时间。

期望以这种方式输出

在此处输入图像描述

标签： mysql

解决方案

您可以在同一张表上使用 INNER JOIN 来执行此操作。

SELECT a.hour, a.Count, b.* FROM user_screenshot as b 
INNER JOIN (
SELECT Hour(created) AS hour, created, COUNT(*) AS Count 
FROM   user_screenshot  
WHERE  created BETWEEN ( Curdate() + INTERVAL (SELECT Hour(Now())) hour - INTERVAL 23 hour ) AND Now()
GROUP BY hour
ORDER  BY ( Curdate() + INTERVAL (SELECT Hour(Now())) hour - INTERVAL 23 hour )
) as a 
ON Hour(a.created) = Hour(b.created) WHERE date(b.created) = '2021-01-27'

推荐阅读