[SQL] 如何获取昨日每个小时的记录数量?

方法

利用左连接来实现逐小时查询(终于把数据库原理学到的知识用上了😂)

select a.click_date,ifnull(b.total,0) as total from (
SELECT date_format(date_sub(curdate(), interval 1 HOUR),'%Y-%m-%d-%H') as click_date 
union all 
SELECT date_format(date_sub(curdate(), interval 2 HOUR),'%Y-%m-%d-%H') as click_date 
union all 
SELECT date_format(date_sub(curdate(), interval 3 HOUR),'%Y-%m-%d-%H') as click_date 
union all 
SELECT date_format(date_sub(curdate(), interval 4 HOUR),'%Y-%m-%d-%H') as click_date 
union all 
SELECT date_format(date_sub(curdate(), interval 5 HOUR),'%Y-%m-%d-%H') as click_date 
union all 
SELECT date_format(date_sub(curdate(), interval 7 HOUR),'%Y-%m-%d-%H') as click_date
union all 
SELECT date_format(date_sub(curdate(), interval 8 HOUR),'%Y-%m-%d-%H') as click_date
union all 
SELECT date_format(date_sub(curdate(), interval 9 HOUR),'%Y-%m-%d-%H') as click_date
union all 
SELECT date_format(date_sub(curdate(), interval 10 HOUR),'%Y-%m-%d-%H') as click_date
union all 
SELECT date_format(date_sub(curdate(), interval 11 HOUR),'%Y-%m-%d-%H') as click_date
union all 
SELECT date_format(date_sub(curdate(), interval 12 HOUR),'%Y-%m-%d-%H') as click_date
union all 
SELECT date_format(date_sub(curdate(), interval 13 HOUR),'%Y-%m-%d-%H') as click_date
union all 
SELECT date_format(date_sub(curdate(), interval 14 HOUR),'%Y-%m-%d-%H') as click_date
union all 
SELECT date_format(date_sub(curdate(), interval 15 HOUR),'%Y-%m-%d-%H') as click_date
union all 
SELECT date_format(date_sub(curdate(), interval 16 HOUR),'%Y-%m-%d-%H') as click_date
union all 
SELECT date_format(date_sub(curdate(), interval 17 HOUR),'%Y-%m-%d-%H') as click_date
union all 
SELECT date_format(date_sub(curdate(), interval 18 HOUR),'%Y-%m-%d-%H') as click_date
union all 
SELECT date_format(date_sub(curdate(), interval 19 HOUR),'%Y-%m-%d-%H') as click_date
union all 
SELECT date_format(date_sub(curdate(), interval 20 HOUR),'%Y-%m-%d-%H') as click_date
union all 
SELECT date_format(date_sub(curdate(), interval 21 HOUR),'%Y-%m-%d-%H') as click_date
union all 
SELECT date_format(date_sub(curdate(), interval 22 HOUR),'%Y-%m-%d-%H') as click_date
union all 
SELECT date_format(date_sub(curdate(), interval 23 HOUR),'%Y-%m-%d-%H') as click_date
union all 
SELECT date_format(date_sub(curdate(), interval 24 HOUR),'%Y-%m-%d-%H') as click_date
) a 
left join (
select date_format(createTime,'%Y-%m-%d-%H') as datetime, count(*) as total from `order` where  `status` = 2 group by date_format(createTime,'%Y-%m-%d-%H')
) b 
on a.click_date = b.datetime

infull用于字段的补0，在无数据的时候可以补上0。

dateSub用于减去时间戳的时间

a主要是用来生成逐小时的时间字段

b主要是查询数据

on是用来执行left join的条件