MySQL 统计近 30 天和近 12 个月的每月记录数

大屏数据有一个折线图，需要近 30 天的和近 12 个月的每月记录数统计。

一开始想的是数据先按照天或者月统计完数据之后，再通过排序解决：

List<DistributionDTO> distributionDTOList = statisticService.selectCarDistribution();
distributionDTOList.sort(Comparator.comparing(DistributionDTO::getDate));

后来考虑到分组查询的不足，如果当月没有记录，那么当月的统计项就会缺失，这与想要的结果不符。

谷歌到 Mysql统计近 30 天的数据，无数据的填充 0，里面提到了生成 n 天日期的 sql 语句：

SELECT
    @s := @s + 1 AS indexs,
    DATE_FORMAT( DATE( DATE_SUB( CURRENT_DATE, INTERVAL @s DAY ) ), '%Y-%m-%d' ) AS dates 
FROM
    mysql.help_topic,
    ( SELECT @s := -1 ) temp  #不想包含当天，@s:=0
WHERE
    @s < 30 
ORDER BY
    dates 

因为变量 @s 是从 -1 + 1 = 0 开始循环生成的，所以生成的日期从当天开始（包含当天）往前数，一直到 30 不满足条件（类似循环里的先执行再循环判断 do..while）才结束。所以上面的查询其实生成了近 31 天日期。

如果只要近 30 天数据（包含当天），则需要 where @s < 30 改成 where @s < 29;
如果不想包含当天，则将 @s 初始值设置为 0，即 select @s := 0。

另外一篇里 查询近 n 天的日期（以查询最近七天为例），只有 FROM 后跟的表名不一样，为 information_schema. TABLES，不了解有什么区别。

主要生成日期的函数 DATE_FORMAT( DATE( DATE_SUB( CURRENT_DATE, INTERVAL @s DAY ) ), '%Y-%m-%d' )，其中最外层为将日期格式化（我这边是 datetime 类型），这里转化为 年-月-日 格式；再往里是日期转化函数 DATE，大概是将时间戳转为日期；最里面的 DATE_SUB 看名称 sub 是减，第一个参数 CURRENT_DATE 返回当前的日期，盲猜第二个参数是减多少天。

菜鸟教程里 MySQL DATE_SUB() 函数 定义：DATE_SUB() 函数从日期减去指定的时间间隔。第二个参数可以取很多，年、月、日、小时、分钟、秒、毫秒等，所以转化为月份统计就是 INTERVAL @s MONTH。

最后，将当前的日期临时表和记录数分组查询的临时表进行左联：

SELECT
    date_table.dates AS date_value,
    IFNULL( temp.count, 0 ) AS num 
FROM
    (
    SELECT
        @s := @s + 1 AS indexs,
        DATE_FORMAT( DATE ( DATE_SUB( CURRENT_DATE, INTERVAL @s DAY ) ), '%Y-%m-%d' ) AS dates 
    FROM
        mysql.help_topic,
        ( SELECT @s := - 1 ) temp #不想包含当天，@s:=0
        
    WHERE
        @s & lt;
    29 
    ORDER BY
        dates 
    ) date_table
LEFT JOIN (
    SELECT LEFT
        ( created_at, 10 ) AS date_value,
        SUM( wireline_num ) AS count 
    FROM
        biz_work_order t1 
    WHERE
        t1.order_state = 7 
    GROUP BY
        LEFT ( created_at, 10 ) 
    ) temp ON date_table.dates = temp.date_value 
ORDER BY
    date_table.dates ASC

其中的 LEFT 函数，就是取左边多少位，用在这里因为存储的是 datetime，日期+时间，需要截取日期部分，才能左联。