mysql 查询本周,本月,本年数据sql

查询当周: 从周一开始计算 (1 ,7) 从周日开始计算就删除1 7

SELECT * FROM 表名 WHERE YEARWEEK(date_format(时间字段,'%Y-%m-%d'),1) = YEARWEEK(now(),7);

查询当月:

SELECT * FROM 表名 WHERE DATE_FORMAT(字段名, '%Y-%m') = DATE_FORMAT(now(),'%Y-%m')

半年内(6个月/如果要改变月份就把6改一下):

SELECT * from 表名 where
 时间字段>DATE_SUB(CURDATE(), INTERVAL 6 MONTH) 

本年

SELECT COUNT(*) FROM 表名 where DATE_FORMAT(时间字段,'%Y') = DATE_FORMAT(SYSDATE(),'%Y')

查询最近7天内的数据:按时间分组

select a.countKey,a.countValue  countValue from
            (select DATE_FORMAT(mycreatedate,'%Y-%m-%d') as countKey,ifnull(COUNT("时间字段"), 0) as countValue  from (
            SELECT curdate() as mycreatedate
            union all
            SELECT date_sub(curdate(), interval 1 day) as mycreatedate
            union all
            SELECT date_sub(curdate(), interval 2 day) as mycreatedate
            union all
            SELECT date_sub(curdate(), interval 3 day) as mycreatedate          
            union all
            SELECT date_sub(curdate(), interval 4 day) as mycreatedate
            union all
            SELECT date_sub(curdate(), interval 5 day) as mycreatedate
            union all
            SELECT date_sub(curdate(), interval 6 day) as mycreatedate
            ) as total
            LEFT JOIN (select * from 表名)  ci   on DATE_FORMAT(ci.时间字段,'%Y-%m-%d')=total.mycreatedate      GROUP BY  mycreatedate )  a