MySQL查询当天&本周&上周&本月&上月&季度&今年的数据

1. 当日

select 字段 from 表名 where 时间字段 BETWEEN CONCAT(CURDATE(),' 00:00:00') AND CONCAT(CURDATE(),' 23:59:59');

2. 昨天

SELECT * FROM `day_sell` WHERE TO_DAYS(NOW()) - TO_DAYS(sell_time) <= 1

3. 近七天

SELECT * FROM `day_sell` WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) < DATE(sell_time);

SELECT * FROM day_sell WHERE YEARWEEK(DATE_FORMAT(sell_time,'%Y-%m-%d')) = YEARWEEK(NOW());

4. 上周 

SELECT * FROM day_sell WHERE YEARWEEK(DATE_FORMAT(sell_time,'%Y-%m-%d')) = YEARWEEK(NOW()) -1;

5. 上个月

SELECT * FROM day_sell WHERE PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(sell_time,'%Y%m')) = 1;

6. 上个季度

SELECT * FROM day_sell WHERE QUARTER(sell_time) = QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER));

7. 本季度

SELECT * FROM `day_sell` WHERE QUARTER(sell_time) = QUARTER(NOW())

8. 今年

SELECT * FROM `day_sell` WHERE YEAR(sell_time) = YEAR(NOW())

9. 计算日期间隔

SELECT DATEDIFF('2020-10-29','2020-10-28') AS days;

10. 按周统计

SELECT DATE_FORMAT(sell_time,'%Y%u') weeks,COUNT(sell_time) COUNT FROM `day_sell` GROUP BY weeks;

11. 按天统计

SELECT DATE_FORMAT(sell_time,'%Y%m%d') days,COUNT(sell_time) COUNT FROM day_sell GROUP BY days;

12. 按月统计

SELECT DATE_FORMAT(sell_time,'%Y%m') months,COUNT(sell_time) COUNT FROM day_sell GROUP BY months;

13. 查询去年

SELECT * FROM day_sell WHERE YEAR(sell_time) = YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR));

14. 查询近6个月的数据

SELECT * FROM day_sell WHERE sell_time BETWEEN DATE_SUB(NOW(),INTERVAL 6 MONTH) AND NOW();