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

创建时间：2017-07-05 投稿人：浏览次数：136

查询每天的数据：

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天的日期

声明变量查询：

SET @counter=0;
select @counter:=@counter+1 as id,count(*) as num FROM testTable ORDER BY num desc

声明：该文观点仅代表作者本人，入门客AI创业平台信息发布平台仅提供信息存储空间服务，如有疑问请联系rumenke@qq.com。