mysql查询当天当周当月的数据

1、查询当天的数据

select * from table_name where TO_DAYS(时间字段)=TO_DAYS(NOW());

2、查询当周的数据

select * from table_name  where YEARWEEK(DATE_FORMAT(时间字段,'%Y-%m-%d'))=YEARWEEK(NOW());

3、查询当月的数据

select * from table_name  where DATE_FORMAT(时间字段,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m');

4、查询昨天的数据

select * from table_name  where TO_DAYS(NOW())-TO_DAYS(时间字段)=1;

5、查询最近7天的数据

select * from table_name  where DATE_SUB(CURDATE(),INTERVAL 7 DAY)<=DATE(时间字段);

6、查询当年的数据

select * from table_name  where YEAR(时间字段) =YEAR(NOW());

7、查询上周的数据

select * from table_name  whereYEARWEEK(DATE_FORMAT(时间字段,'%Y-%m-%d'))=YEARWEEK(NOW())-1;

8、查询上月的数据

select *from table_name 
where PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(时间字段,'%Y%m');

9、查询本季度的数据

select * from table_name  where QUARTER(时间字段)=QUARTER(now());

10、查询上季度的数据

select * from table_name  where QUARTER(时间字段)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

11、查询本年的数据

select * from table_name  where YEAR(时间字段)=YEAR(NOW());

12、查询上年的数据

select * from table_name  where year(时间字段)=year(date_sub(now(),interval 1 year));

13、按照天统计数据

SELECT
    count(id) countNum,
    DATE(时间字段) createTime
FROM
    baj_checkimgs
GROUP BY
    DATE(时间字段)
ORDER BY
    DATE(时间字段) DESC;

14、按照周统计数据

SELECT
    count(id) countNum,
    WEEK(时间字段) createTime
FROM
    baj_checkimgs
GROUP BY
    WEEK(时间字段)
ORDER BY
    WEEK(时间字段) DESC;

15、按月统计

SELECT
    count(id) countNum,
    MONTH(时间字段) createTime
FROM
    baj_checkimgs
GROUP BY
    MONTH(时间字段)
ORDER BY
    MONTH(时间字段) DESC;

16、按照季度统计

SELECT
    count(id) countNum,
    QUARTER(时间字段) createTime
FROM
    baj_checkimgs
GROUP BY
    QUARTER(时间字段)
ORDER BY
    QUARTER(时间字段) DESC;

17、按照年统计

SELECT
    count(id) countNum,
    YEAR(时间字段) createTime
FROM
    baj_checkimgs
GROUP BY
    YEAR(时间字段)
ORDER BY
    YEAR(时间字段) DESC;