SELECT
DATE_FORMAT(month_dates.month_start, '%Y-%m') AS Month,
IFNULL(COUNT(t.create_time), 0) AS RecordCount
FROM
(
SELECT @cdate := date_add( @cdate, INTERVAL - 1 MONTH ) month_start
FROM
( SELECT @cdate := date_add( CURDATE(), INTERVAL 1 MONTH ) FROM t_abc ) car limit 7
) AS month_dates
LEFT JOIN
t_abc t ON DATE_FORMAT(t.create_time, '%Y-%m') = DATE_FORMAT(month_dates.month_start, '%Y-%m')
GROUP BY
Month
ORDER BY
Month;
-
生成近7个月的月份序列:
-
初始化变量
@cdate
:- 首先,在子查询
(SELECT @cdate := DATE_ADD(CURDATE(), INTERVAL 1 MONTH) FROM t_abc)
中,使用CURDATE()
函数获取当前日期,然后通过DATE_ADD()
函数将当前日期向前推进1个月,得到的结果赋值给用户定义变量@cdate
。这里使用t_battery
表进行查询实际上是为了执行初始化操作,因为MySQL不允许直接在顶层查询外定义和初始化变量而不引用任何表,但实际上并不从t_abc
表中取出数据。
- 首先,在子查询
-
生成月份序列:
- 在外部查询中,
SELECT @cdate := DATE_ADD(@cdate, INTERVAL - 1 MONTH) month_start
语句每次迭代都将@cdate
变量的值减少1个月,生成一个新的月份作为month_start
列的值。 FROM (子查询) car
部分确保了初始化步骤被执行,而LIMIT 7
确保只生成7个这样的月份记录。
- 在外部查询中,
-
结果输出:
- 最终,这个查询会输出7行数据,每一行代表一个月份,格式为'YYYY-MM',从当前日期的下一个月开始倒推7个月。
-
-
左连接到实际数据表:
- 主查询部分通过
LEFT JOIN
将生成的月份序列month_dates
表与实际的记录表t_abc
进行连接。连接条件是将t_abc
表中的create_time
字段(记录创建时间)按月格式化后,与月份序列中的月份匹配。LEFT JOIN
确保了即使某些月份在t_abc
表中没有对应记录,也会出现在结果集中。
- 主查询部分通过
-
计算每个月的记录数量:
- 使用
IFNULL(COUNT(t.create_time), 0)
来计算每个月的记录数量。COUNT(t.create_time)
计算每个月实际的记录数,而IFNULL
函数确保了如果某个月份没有记录(即COUNT
结果为NULL
),则将其替换为0,以完整展示每个月的数据情况。
- 使用
-
分组并排序:
GROUP BY Month
按照月份对结果进行分组,确保每个唯一的月份只有一行数据,展示了该月的记录总数。ORDER BY Month
按月份升序排序最终的结果集,使得月份序列有序展现。
综上所述,此查询提供了未来7个月内,每个月在t_abc
表中创建记录的统计情况,即便某月无记录也明确标记为0条。