Mysql统计每天 每周 每年订单量

Mysql统计每天 每周 每年订单量

统计一周订单量

SELECTDATE( create_date ) AS date,COUNT( 1 ) AS count 
FROMorders 
WHEREDATE_SUB( CURDATE( ), INTERVAL 7 DAY ) <= DATE( create_date ) 
GROUP BYdate;

sql 语句解析

• date(create_date): 将日期格式化为 2000-01-01这种格式
• curdate() :获取当前日期 2020-06-30 等同于 date(now())
• date_sub(curdate(), interval 7 day): 获取7天前的日期

但该语句不会显示没有订单的日期,参考其他博客将sql优化为如下

select a.date,unt,0) as count
from (SELECT curdate() as dateunion allSELECT date_sub(curdate(), interval 1 day) as click_dateunion allSELECT date_sub(curdate(), interval 2 day) as click_dateunion allSELECT date_sub(curdate(), interval 3 day) as click_dateunion allSELECT date_sub(curdate(), interval 4 day) as click_dateunion allSELECT date_sub(curdate(), interval 5 day) as click_dateunion allSELECT date_sub(curdate(), interval 6 day) as click_date
) a left join (select date(create_date) as createdate, count(*) as countfrom ordersgroup by date(create_date)
) b on a.date = b.createdate;

统计每年的订单量

SELECT YEAR(create_date) as year,COUNT(1) as count FROM orders  GROUP BY year;

统计今年每个月的订单量

SELECT MONTH(create_date) as month,COUNT(1) as count FROM orders WHERE YEAR(create_date) = YEAR(NOW()) GROUP BY month ORDER BY month;

统计今年每周的订单量

SELECT WEEK(create_date) as week,COUNT(1) as count FROM orders WHERE YEAR(create_date) = YEAR(NOW()) GROUP BY week ORDER BY week;