目录
1 所需基础日期查询实现
2 根据业务表实际查询操作
2.1 简单查询
2.2 查询优化：
2.3 在mybatis中 查询，关联业务信息实现
1 所需基础日期查询实现
（1）在计算同比环比时需要用到本日、本月1日、上月本日、上月1日，年初 及去年对应的时间
select CURDATE() as cur_day,DATE_FORMAT(CURDATE(),'%Y-%m-01') as cur_month_first_day,DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY) as cur_year_first_day,DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),'%Y-%m-01') as last_month_first_day,DATE_SUB(CURDATE(),INTERVAL 1 MONTH) as last_month_cur_day,DATE_SUB(CURDATE(),INTERVAL 1 YEAR) as last_year_cur_day,DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 YEAR),'%Y-%m-01') as last_year_cur_month_first_day,DATE_SUB(DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY),INTERVAL 1 YEAR) as last_year_first_day,DATE_SUB(DATE_SUB(CURDATE(),INTERVAL 1 MONTH), INTERVAL 1 YEAR) as last_year_last_month_cur_day,DATE_FORMAT(DATE_SUB(DATE_SUB(CURDATE(),INTERVAL 1 MONTH), INTERVAL 1 YEAR),'%Y-%m-01') as last_year_last_month_first_day
（2）本周一、本周日、上周一、上周日
select subdate(curdate(),date_format(curdate(),'%w')-1) as thisWeekMonday,subdate(curdate(),date_format(curdate(),'%w')-7) as thisWeekSunday,subdate((subdate(curdate(),7)), date_format(subdate(curdate(),7),'%w')-1) as lastWeekMonday,subdate((subdate(curdate(),7)), date_format(subdate(curdate(),7),'%w')-7) as lastWeekSunday
2 根据业务表实际查询操作
2.1 简单查询
示例：客流统计
select -- 日数据(-- DATE_FORMAT(CURDATE(),'%Y-%m-%d') as data_time, entity_code,entity_name,select sum(counts) as count from sta_passenger_analysis where DATE_FORMAT(data_time,'%Y-%m-%d')= DATE_FORMAT(CURDATE(),'%Y-%m-%d')) as cur_day_count,-- 上月本日数据(-- DATE_SUB(CURDATE(),INTERVAL 1 MONTH) as data_time,entity_code,entity_name,select sum(counts) as count from sta_passenger_analysis where DATE_FORMAT(data_time,'%Y-%m-%d')= DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),'%Y-%m-%d')) as last_month_cur_day_count,-- 去年本日数据(-- DATE_SUB(CURDATE(),INTERVAL 1 YEAR) as data_time,entity_code,entity_name,select sum(counts) as count from sta_passenger_analysis where DATE_FORMAT(data_time,'%Y-%m-%d')= DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 YEAR),'%Y-%m-%d')) as last_year_cur_day_count,-- 本月累计(select sum(counts) as count from sta_passenger_analysis where 1=1AND DATE_FORMAT(data_time,'%Y-%m-%d')<= DATE_FORMAT(CURDATE(),'%Y-%m-%d')AND DATE_FORMAT(data_time,'%Y-%m-%d')>=DATE_FORMAT(CURDATE(),'%Y-%m-01')) as cur_month_month_count,-- 上月累计(select sum(counts) as count from sta_passenger_analysis where 1=1AND DATE_FORMAT(data_time,'%Y-%m-%d')<= DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),'%Y-%m-%d')AND DATE_FORMAT(data_time,'%Y-%m-%d')>=DATE_FORMAT(DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),'%Y-%m-01'),'%Y-%m-01')) as last_month_month_count,-- 去年本月累计(select sum(counts) as count from sta_passenger_analysis where 1=1AND DATE_FORMAT(data_time,'%Y-%m-%d')<= DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 YEAR),'%Y-%m-%d')AND DATE_FORMAT(data_time,'%Y-%m-%d')>=DATE_FORMAT(DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 YEAr),'%Y-%m-01'),'%Y-%m-01')) as last_year_cur_month_month_count,-- 今年累计数据(select sum(counts) as count from sta_passenger_analysis where 1=1AND DATE_FORMAT(data_time,'%Y-%m-%d')<= DATE_FORMAT(CURDATE(),'%Y-%m-%d')AND DATE_FORMAT(data_time,'%Y-%m-%d')>=DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY),'%Y-%m-01')) as cur_year_year_count,-- 去年累计数据(select sum(counts) as count from sta_passenger_analysis where 1=1AND DATE_FORMAT(data_time,'%Y-%m-%d')<= DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 YEAR) ,'%Y-%m-%d')AND DATE_FORMAT(data_time,'%Y-%m-%d')>=DATE_FORMAT(DATE_SUB(DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY),INTERVAL 1 YEAR),'%Y-%m-01')) as last_year_year_count
2.2 查询优化：
对表名提取，先做一次过滤后查询(过滤无效)
2.3 在mybatis中 查询，关联业务信息实现
mybatis xml中不支持小于号，需使用<![CDATA[ **** ]]> 将小于的比较判断包含在[]内
增长比例在此未计算，可在前端根据数据自行计算
如果觉得《同比环比日期计算 本周一 本周日 上周一 上周日（mysql）》对你有帮助，请点赞、收藏，并留下你的观点哦！
