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

查询每天的数据：

1. SELECT

2. COUNT(1) AS countNumber,

3. DATE_FORMAT(createTime,'%Y-%m-%d') AS dateTime

4. FROM

5. testTable

6. GROUP BY DATE_FORMAT(createTime,'%Y-%m-%d')

查询每周的数据：

 

 

1. SELECT

2. COUNT(1) AS countNumber,

3. WEEK(createTime) as dateTime

4. FROM

5. testTable

6. GROUP BY WEEK(createTime)

查询每月的数据：

 

 

1. SELECT

2. COUNT(1) AS countNumber,

3. MONTH(createTime) as dateTime

4. FROM

5. testTable

6. GROUP BY MONTH(createTime)

查询每年的数据：

 

 

1. SELECT

2. COUNT(1) AS countNumber,

3. YEAR(createTime) as dateTime

4. FROM

5. testTable

6. 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