MySQL查询本周、上周、本月、上个月份数据的sql代码

ゞ浴缸里的玫瑰 2022-06-07 14:19 230阅读 0赞

文章装载自 [原文作者：宋发元 原文链接：http://blog.csdn.net/u011019141][_http_blog.csdn.net_u011019141]

查询当前这周的数据   
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')   
  
查询距离当前现在6个月的数据   
select name,submittime from enterprise where submittime between date\_sub(now(),interval 6 month) and now();   
  
查询上个月的数据   
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') ;   
  
select \* from user where WEEKOFYEAR(FROM\_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now())   
  
select \*   
from user   
where MONTH(FROM\_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now())   
  
select \*   
from \[user\]   
where YEAR(FROM\_UNIXTIME(pudate,'%y-%m-%d')) = YEAR(now())   
and MONTH(FROM\_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now())   
  
select \*   
from \[user\]   
where pudate between 上月最后一天   
and 下月第一天

[_http_blog.csdn.net_u011019141]: http://blog.csdn.net/u011019141