mysql统计每天,每周,每小时,每分钟不同时间段的数据

首先看到我的数据表

现在想要获取一段时间之内的订单的数量

1.按月进行统计

select 
DATE_FORMAT(create_time,'%Y-%m') modifiedDay,
COUNT(order_id) num_count
from order_master 
where create_time BETWEEN '2019-10-01 10:17:11' AND '2019-11-26 10:17:11'
GROUP BY modifiedDay
ORDER BY modified_time

效果图

2.按天进行统计

select 
DATE_FORMAT(create_time,'%Y-%m-%d-%W') createTime,
COUNT(order_id) num_count
from order_master 
where create_time BETWEEN '2019-10-01 10:17:11' AND '2019-11-26 10:17:11'
GROUP BY createTime
ORDER BY create_time

效果图

3.按小时进行统计

select 
DATE_FORMAT(create_time,'%Y-%m-%d-%W-%H') createTime,
COUNT(order_id) num_count
from order_master 
where create_time BETWEEN '2019-10-01 10:17:11' AND '2019-11-26 10:17:11'
GROUP BY createTime
ORDER BY create_time

4.按分钟进行统计

select 
DATE_FORMAT(create_time,'%Y-%m-%d-%W-%H:%i') createTime,
COUNT(order_id) num_count
from order_master 
where create_time BETWEEN '2019-10-01 10:17:11' AND '2019-11-26 10:17:11'
GROUP BY createTime
ORDER BY create_time

效果图

5.按秒钟进行统计

select 
DATE_FORMAT(create_time,'%Y-%m-%d-%W-%H:%i:%S') createTime,
COUNT(order_id) num_count
from order_master 
where create_time BETWEEN '2019-10-01 10:17:11' AND '2019-11-26 10:17:11'
GROUP BY createTime
ORDER BY create_time

效果图