mysql YEARWEEK(date[,mode]) 函数

mysql YEARWEEK(date[,mode]) 函数 查询上周数据

通常使用下边sql即可(如果数据库设置了周一为一周起始的话)：

-- 查询上周数据(addtime为datetime格式)
SELECT id,addtime FROM mall_order WHERE YEARWEEK(date_format(addtime,'%Y-%m-%d')) = YEARWEEK(now())-1; 

YEARWEEK(date[,mode]) 函数默认西方周日当做一周的起始,需要改成国人的使用习惯，用周一作为一周起始，这里设置YEARWEEK的第二个参数mode为1即可。

由于每个数据库的配置不一样，所以最好还是先执行下边的sql语句人工审查一下数据结果的第一条和最后一条数据的时间是否是上周的周一和周末，再考虑是否给mode赋值

-- 查询上周数据(addtime为时间戳) 
SELECT id,addtime FROM mall_order WHERE YEARWEEK(FROM_UNIXTIME(addtime,'%Y-%m-%d'),1) = YEARWEEK(now(),1)-1; 
-- 查询上周数据(addtime为datetime格式)
SELECT id,addtime FROM mall_order WHERE YEARWEEK(date_format(addtime,'%Y-%m-%d'),1) = YEARWEEK(now(),1)-1; 

补充：

-- 查询当前这周的数据 
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());
 
-- 查询上周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;
 
 
-- 查询当前月份的数据
select name,submittime from enterprise  where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')

--查询上个月的数据
select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m');
select * from `user` where DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ;

--查询距离当前现在6个月的数据
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();