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/