MySQL查询某个时间段内所有的记录总数

因为需要完成如下图的效果需要查询出最近一个时间段内所有的记录总数

有两种方法HOUR()和DATE_FORMAT()。

HOUR()使用时如果计算最近一天的记录需要添加过滤条件，而DATE_FORMAT()不需要添加过滤条件默认查询最近一天的记录

例子：

SELECT
thermometer_return as '是否冷链',
COUNT(id) as '数量',
HOUR (create_date) as '时间'
FROM
order_sale
WHERE
company_id = '2'
AND del_flag = '0'
AND create_date >= '2017-07-25 00:00:00.0'
AND create_date <= '2017-07-25 12:00:00.0'
GROUP BY
HOUR (create_date),
thermometer_return
ORDER BY
create_date

SELECT DATE_FORMAT(create_date, '%H') as '时间', COUNT(*) as '记录总数',thermometer_return as '是否冷链' FROM order_sale
WHERE
company_id = '2'
AND del_flag = '0'
-- AND create_date >= '2017-07-25 00:00:00.0'
-- AND create_date <= '2017-07-25 12:00:00.0'
GROUP BY DATE_FORMAT(create_date, '%H'),thermometer_return;