MySQL按月查询 mysql查询每月数据

查询每天的数据

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

COUNT(1) AS countNumber 统计1字段的结果计数并重命名为countNumber

DATE_FORMAT(createTime,'%Y-%m-%d') AS dateTime 统计每天的数据并重命名为dateTime
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