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

来源:转载

查询每天的数据:

SELECTCOUNT(1) AS countNumber,DATE_FORMAT(createTime,'%Y-%m-%d') AS dateTimeFROMtestTableGROUP BY DATE_FORMAT(createTime,'%Y-%m-%d')

查询每周的数据:

SELECTCOUNT(1) AS countNumber,WEEK(createTime) as dateTimeFROMtestTableGROUP BY WEEK(createTime) 

查询每月的数据:

SELECTCOUNT(1) AS countNumber,MONTH(createTime) as dateTimeFROMtestTableGROUP BY MONTH(createTime)
查询每年的数据:

SELECTCOUNT(1) AS countNumber,YEAR(createTime) as dateTimeFROMtestTableGROUP BY YEAR(createTime)

其它:

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

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

声明变量查询:

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

分享给朋友:
您可能感兴趣的文章:
随机阅读: