mysql 中關於本周、上周的表示

標籤：

本周：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‘)

曆史月份：在後面減去相應的月份數

mysql 中關於本周、上周的表示