mysql查询每天每周每月每年的数据方法

查询每天的数据：

SELECT
COUNT(1) AS countNumber,
DATE_FORMAT(createTime,’%Y-%m-%d’) AS dateTime
FROM
testTable
GROUP BY DATE_FORMAT(createTime,’%Y-%m-%d’);

查询每周的数据：

SELECT
COUNT(1) AS countNumber,
WEEK(createTime) as dateTime
FROM
testTable
GROUP BY WEEK(createTime) ;

查询每月的数据：

SELECT
COUNT(1) AS countNumber,
MONTH(createTime) as dateTime
FROM
testTable
GROUP BY MONTH(createTime);

查询每年的数据：
SELECT
COUNT(1) AS countNumber,
YEAR(createTime) as dateTime
FROM
testTable
GROUP BY YEAR(createTime);

其它：
DATE_ADD(createTime,INTERVAL 1 DAY) 得到指定日期后一天的日期/把1改为任意数字就可以得到后N天的日期

DATE_SUB(createTime,INTERVAL 1 DAY) 得到指定日期前一天的日期/把1改为任意数字就可以得到前N天的日期