思路:
查询本年订单:
只需要把订单签收时间截取为年,where条件判断为今年,在把订单签收时间截取为月根据月分组,sum(订单价格),这样子就可以查询出一年中每个月的订单金额
查询本月本周订单:
和查询年订单差不多,截取签收时间为月周,在根据签收时间截取为周天,分组就可以了
代码年每个月:
-- omSumAmountActuallyPaid为订单金额
-- omReceivingDate为签收时间
-- ordermaster 为订单表
-- date_format(omReceivingDate,'%m') 将时间转化为月
--year(omReceivingDate) 将时间转化为年
select sum(omSumAmountActuallyPaid),date_format(omReceivingDate,'%m') as m from ordermaster o
year(omReceivingDate)=YEAR(CURDATE()) GROUP BY m
查询结果
代码周每天:
-- YEARWEEK(date,1)中国的周
-- YEARWEEK(date)外国星期天就算下一周了
SELECT sum(omSumAmountActuallyPaid) as money, date_format(omReceivingDate,'%w') as id FROM orderMaster o YEARWEEK(date_format(omReceivingDate,'%Y-%m-%d'),1) = YEARWEEK(CURDATE(),1) GROUP BY id;
查询结果![在这里插入图片描述](https://img-blog.csdnimg.cn/2020052215355232.png)
其他也都差不多,思路都一样,转换日期,分组