SQL查询最近12个月的数据量 没有补0

需求：查询最近12个月的数据量，此处表的名称为：ticket_ticket

按月查询数据，sql语句如下：

SELECT COUNT(*) as num, DATE_FORMAT(create_at,'%Y-%m') as t
FROM ticket_ticket WHERE flow_id=336 GROUP BY DATE_FORMAT(create_at,'%Y-%m')

查询结果：

可以看出只有两个月份，不满足需求。

解决方案如下：

步骤一：生成一个月份表，包含最近的12个月

sql如下：

SELECT
	DATE_FORMAT(@cdate := date_add( @cdate, INTERVAL - 1 MONTH ),'%Y-%m') as date
FROM
	( 
	SELECT @cdate := date_add(CURDATE(), INTERVAL 1 MONTH )
	FROM ticket_ticket LIMIT 12)d
	ORDER BY date

结果如下：

步骤二：将查询结果表并入月份表

sql语句：

SELECT * FROM (
SELECT
	DATE_FORMAT(@cdate := date_add( @cdate, INTERVAL - 1 MONTH ),'%Y-%m') as date
FROM
	( 
	SELECT @cdate := date_add(CURDATE(), INTERVAL 1 MONTH )
	FROM ticket_ticket LIMIT 12)d
	ORDER BY date
)date_c LEFT JOIN (
SELECT COUNT(*) as num, DATE_FORMAT(create_at,'%Y-%m') as t
FROM ticket_ticket WHERE flow_id=336 GROUP BY DATE_FORMAT(create_at,'%Y-%m')
)tab ON t=date

结果如下：

步骤三：处理查询结果：NULL设置为0，并按照月份排序

sql语句：

SELECT date as 月份, IFNULL(tab.num, 0) as 数量 FROM (
SELECT
	DATE_FORMAT(@cdate := date_add( @cdate, INTERVAL - 1 MONTH ),'%Y-%m') as date
FROM
	( 
	SELECT @cdate := date_add(CURDATE(), INTERVAL 1 MONTH )
	FROM ticket_ticket LIMIT 12)d
	ORDER BY date
)date_c LEFT JOIN (
SELECT COUNT(*) as num, DATE_FORMAT(create_at,'%Y-%m') as t
FROM ticket_ticket WHERE flow_id=336 GROUP BY DATE_FORMAT(create_at,'%Y-%m')
)tab ON t=date

结果如下：

总结这里用到的sql语句

• DATE_FORMAT(date,format)
  定义：用于以不同的格式显示日期/时间数据。即对时间进行格式转换
  用法：date 参数是合法的日期。format 规定日期/时间的输出格式。

DATE_FORMAT('2021-02-12','%Y-%m')

输出：2021-02

• CURDATE()
  定义：返回当前日期
  用法：直接使用CURDATE()

SELECT NOW(),CURDATE(),CURTIME()

结果：