mysql 中关于周和月份的表示

时间:2023-04-07 13:33:20

本周:YEARWEEK(date_format(create_time,'%Y-%m-%d')) = YEARWEEK(now())

上周:YEARWEEK(date_format(create_time,'%Y-%m-%d')) = YEARWEEK(now())-1

上上周:YEARWEEK(date_format(create_time,'%Y-%m-%d')) = YEARWEEK(now())-2

注意,历史周只需要在YEARWEEK(now())后面减去相应的数字

下周:YEARWEEK(date_format(create_time,'%Y-%m-%d')) = YEARWEEK(now())+1

注意:未来周就在后面+1

select sum(total_money) as 本周卖出总金额 from crm_order_record where YEARWEEK(date_format(create_time,'%Y-%m-%d')) = YEARWEEK(now())
and refund_money=0;

本月:date_format(create_time,'%Y-%m')=date_format(now(),'%Y-%m')

上月:date_format(create_time,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')

上上月:date_format(create_time,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 2 MONTH),'%Y-%m')

历史月份:在后面减去相应的月份数