sql实现查询两个时间之间每月的数量

       在工作中，我们有时候会出现根据两个时间，求出来两个时间之间每个月的数据数量，多用于给折线图、柱状图提供数据的情景，之前遇到过一次，找到了一个比较好的处理方法，只需要使用sql语句就可以实现了。

我们最终需要给如下图供数据：

组件要求提供的数据模型如下图：

         我们已单表为例，时间字段为alarm_time ,然后根据该字段的时间，计算出当年每个月有多少条数据，上sql。感觉想法不错，将alarm_time 格式化为只有月，然后再根据这个月进行分组，结合聚合函数直接就可以达到效果！

SELECT 
 to_char(alarm_time,'YYYY-MM'),
 count( 1 ) 
FROM
 alarm_record 
WHERE
 alarm_time > to_date ( '2019-10', 'YYYY-MM' ) 
 AND alarm_time < to_date ( '2020-09', 'YYYY-MM' ) 
 GROUP BY  to_char(alarm_time,'YYYY-MM')
 ORDER BY to_char(alarm_time,'YYYY-MM')
 

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

优化：

上边的sql查询时，如果某个月每月没有数据，则最终查询出来时这个月也没有值，效果是如下图：

如上，查询结果中没有2020-05的数据，而我们现在需求一般都要求没有数据的月份应该是显示0，我们将sql修改为如下后即可

实现如果某个月没有数据，则也能查询出该月份，并且数量为0，如下：

Postgresql写法如下：

SELECT
	monthdate,
	count( b.* ) 
FROM
	(
SELECT
	to_char ( date, 'YYYY-MM' ) AS monthdate 
FROM
	generate_series ( date_trunc ( 'month', to_date ( '201910', 'yyyymm' ) ),
 date_trunc ( 'month', to_date ( '202009', 'yyyymm' ) ), '1 month' ) AS time( date ) 
	) a
	LEFT JOIN alarm_record b ON monthdate = to_char ( b.alarm_time, 'YYYY-MM' ) 
GROUP BY
	monthdate 
ORDER BY
	monthdate

 Mysql写法如下：

SELECT
	month,
	count( b.* ) 
FROM
	(
 SELECT
	concat( DATE_FORMAT( m1, '%m' ), "月" ) as month
FROM
	(
		SELECT
			CONCAT(YEAR ( now( ) ),'-01-01') + INTERVAL m MONTH AS m1 
		FROM
			(
				SELECT
					@rownum := @rownum + 1 AS m 
				FROM
					( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) t1,
					( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) t2,
					( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) t3,
					( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) t4,
					( SELECT @rownum :=- 1 ) t0 
			) d1 
	) d2 
WHERE
	m1 <= now( ) 
ORDER BY
	m1
	) a
	LEFT JOIN alarm_record b ON month = date_format( b.alarm_time, '%Y-%m' ) 
GROUP BY
	month
ORDER BY
	month

#@lehao#

帮助到您请点赞关注收藏谢谢！！