sql查询当天、本周、本月、本年的数据
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))
更多推荐
已为社区贡献1条内容
所有评论(0)