sql查询当天、本周、本月、本年的数据

查询当天

select * from tb where DATE(create_time) = curdate()

查询本周

select * from tb where YEARWEEK(date_format(create_time,'%Y-%m-%d')) = YEARWEEK(now())

值得一说的是，这里的本周是从上个星期的周日开始计算，到这个星期的周六。

查询上周

SELECT * FROM tb WHERE WEEK(create_time)=WEEK(DATE_SUB(NOW(),INTERVAL 1 WEEK))
AND YEAR(create_time) = YEAR(NOW()) 

查询近七天的数据

select * from tb where date_sub(curdate(), interval 7 day) <= date(create_time)

查询本月

select * from tb where date_format(create_time,'%Y-%m') = date_format(now(),'%Y-%m')

查询上月的数据

select * from tb
	where date_format(create_time, '%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m') 

查询本季度

SELECT * FROM tb WHERE QUARTER(create_time)=QUARTER(NOW())
AND YEAR(create_time) = YEAR(NOW())

查询上季度

SELECT * FROM tb WHERE QUARTER(create_time)=QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER)) AND YEAR(create_time) = YEAR(NOW())

查询本年

select * from tb where date_format(create_time,'%Y') = date_format(now(),'%Y')

查询上年

SELECT * FROM tb WHERE YEAR(create_time)=YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR))