mysql按照每个小时查询_如何使用MySql中的单个查询按小时分类？

这是一个非常好的开始.这适用于任何日期/时间范围.但是,它有一个主要先决条件：您需要创建一个带有dt_hr datetime字段的间隔表,该字段包含您扫描的所有间隔.

Ex: '2011-12-25 00:00:00',

'2011-12-25 01:00:00',

'2011-12-25 02:00:00',

'2011-12-25 03:00:00',

. . .

'2011-12-25 23:00:00'

–

SELECT DATE_FORMAT(intervals.dt_hr,'%m/%d/%Y') AS Date,

EXTRACT(HOUR FROM intervals.dt_hr) AS Hour,

CASE

WHEN intervals.dt_hr > TIMESTAMPADD(HOUR,HOUR(s2.start_dts), DATE(s2.start_dts))

AND intervals.dt_hr < TIMESTAMPADD(HOUR,HOUR(s2.end_dts), DATE(s2.end_dts))

THEN 60

WHEN intervals.dt_hr = TIMESTAMPADD(HOUR,HOUR(s2.start_dts), DATE(s2.start_dts))

AND intervals.dt_hr < TIMESTAMPADD(HOUR,HOUR(s2.end_dts), DATE(s2.end_dts))

THEN 60 - EXTRACT(MINUTE FROM s2.start_dts)

WHEN intervals.dt_hr = TIMESTAMPADD(HOUR,HOUR(s2.end_dts), DATE(s2.end_dts))

AND intervals.dt_hr > TIMESTAMPADD(HOUR,HOUR(s2.start_dts), DATE(s2.start_dts))

THEN EXTRACT(MINUTE FROM s2.end_dts)

WHEN intervals.dt_hr = TIMESTAMPADD(HOUR,HOUR(s2.start_dts), DATE(s2.start_dts))

AND intervals.dt_hr = TIMESTAMPADD(HOUR,HOUR(s2.end_dts), DATE(s2.end_dts))

THEN EXTRACT(MINUTE FROM s2.end_dts) - EXTRACT(MINUTE FROM s2.start_dts)

ELSE 0

END AS MinutesOnLine

FROM intervals

LEFT JOIN sessions s2

ON intervals.dt_hr >= TIMESTAMPADD(HOUR,HOUR(s2.start_dts), DATE(s2.start_dts))

AND intervals.dt_hr <= TIMESTAMPADD(HOUR,HOUR(s2.end_dts), DATE(s2.end_dts))

要生成区间表,您可以创建一个存储过程,该过程创建一个带有date_hour序列的临时表.有关执行此操作的方法,请参阅Get a list of dates between two dates.