Mysql 查询上周的数据,Mysql查询本周的数据
================================
©Copyright 蕃薯耀 2022-09-27
https://www.cnblogs.com/fanshuyao/
一、Mysql获取周的时间
#当前一周的第一天:
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 0 DAY) WeekFirst;
#当前一周的最后一天:
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 6 DAY) WeekLast;
#上一周的第一天:
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) +7 DAY) LastWeekFirst;
#上一周的最后一天:
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) +1 DAY) LastWeekLast;
#时间推算(以周日算):
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 8 DAY) LastWeekFirst; SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) +(8 + 7) DAY) LastWeekFirst; SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) +(8 + 14) DAY) LastWeekFirst;
#上一周的区间
AND DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 7 DAY) <= DATE(修改成自己的时间字段) AND DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 0 DAY) > DATE(修改成自己的时间字段)
周时间通用计算公式:
weeks :1表示当前周,2表示上一周 //当前周周一开始日:7 - 7 + (weeks - 1) * 7 //当前周周日开始日:8 - 7 + (weeks - 1) * 7
二、示例代码(C#)
java转C#的苦B!
/// <summary> /// 周统计 /// </summary> /// <param name="week">1表示当前周,2表示上一周</param> /// <returns></returns> public WebResponseContent SumByWeek(int week) { WebResponseContent webResponse = new WebResponseContent(); //当前周以周一开始日:7-7 + (weeks - 1)*7 //当前周以周日开始日:8 - 7 + (weeks - 1) * 7 short WeekStart = (short)Week.MondayStart; short DaysOfWeek = (short)Week.DaysOfWeek; int nowWeekStart = (WeekStart - DaysOfWeek + (week - 1) * DaysOfWeek); int lastWeekStart = (WeekStart - DaysOfWeek + week * DaysOfWeek); Logger.Info($"SumByWeek nowWeekStart = {nowWeekStart}"); Logger.Info($"SumByWeek lastWeekStart = {lastWeekStart}"); List<MySqlParameter> mySqlParameters = new List<MySqlParameter>(); StringBuilder sqlBuilder = new StringBuilder(""); sqlBuilder.Append(" ").Append("SELECT IFNULL(SUM(d.Quantity), 0) 'Sum'"); sqlBuilder.Append(" ").Append("FROM 主表 d "); sqlBuilder.Append(" ").Append("INNER JOIN 从表 si ON si.StockInId = d.StockInId "); sqlBuilder.Append(" ").Append("WHERE 1=1 "); sqlBuilder.Append(" ").Append("AND si.ApprovalStatus=?ApprovalStatus"); mySqlParameters.Add(new MySqlParameter("?ApprovalStatus", (short)StockInStatus.In)); if (week == 1) { sqlBuilder.Append(" ").Append("AND DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + ?nowWeekStart DAY) <= DATE(si.CreateDate)"); mySqlParameters.Add(new MySqlParameter("?nowWeekStart", nowWeekStart)); } else { sqlBuilder.Append(" ").Append("AND DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + ?lastWeekStart DAY) <= DATE(si.CreateDate)"); sqlBuilder.Append(" ").Append("AND DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + ?nowWeekStart DAY) > DATE(si.CreateDate)"); mySqlParameters.Add(new MySqlParameter("?lastWeekStart", lastWeekStart)); mySqlParameters.Add(new MySqlParameter("?nowWeekStart", nowWeekStart)); } Logger.Info($"SumByWeek sqlBuilder.ToString() = {sqlBuilder.ToString()}"); var sum_model = _repository.GetOneBysql<Sum_model>(sqlBuilder.ToString(), mySqlParameters.ToArray()); return webResponse.OK("成功", sum_model.Sum); }
(时间宝贵,分享不易,捐赠回馈,^_^)
================================
©Copyright 蕃薯耀 2022-09-27
https://www.cnblogs.com/fanshuyao/
今天越懒,明天要做的事越多。
分类:
蕃薯耀分享
, Database数据库
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· Manus重磅发布:全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后,我竟然真的恢复了删除的微信聊天记录!
· 没有Manus邀请码?试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 【自荐】一款简洁、开源的在线白板工具 Drawnix