MySQL查询每个月的数量, 以及每个月的同比环比数据

说明 : a_query表就是一张空表

# 查询今年案件上报每个月的数量, 以及每个月上报案件的同比环比数据
SELECT
	monthData.months AS '月份',
	monthData.thisMonth AS '月数量',
	IFNULL( CONVERT( ( (monthData.thisMonth - monthData.lastYearThisMonth) / monthData.lastYearThisMonth *100), DECIMAL(10,2) ), 0) AS '同比', 
	IFNULL( CONVERT( ( (monthData.thisMonth - monthData.lastMonth) / monthData.lastMonth *100), DECIMAL(10,2) ), 0) AS '环比'
FROM(	
	SELECT 
		monthTable.months AS months,
		(
			SELECT 
			   COUNT(id) AS tCount
			FROM
			    case_report
			WHERE
			    is_delete = 0 AND DATE_FORMAT(case_report_time, '%Y-%m') = monthTable.months AND case_area_code LIKE('150201%')
		) AS thisMonth,
		(
			SELECT 
			   COUNT(id) AS tCount
			FROM
			    case_report
			WHERE
			    is_delete = 0 AND PERIOD_DIFF(DATE_FORMAT(STR_TO_DATE(CONCAT(monthTable.months,'-01'),"%Y-%m-%d"), '%Y%m' ), DATE_FORMAT(case_report_time, '%Y-%m' )) = 1
			    AND case_area_code LIKE('150201%')
		) AS lastMonth,
		(
			SELECT 
			   COUNT(id) AS tCount
			FROM
			    case_report
			WHERE
			    DATE_FORMAT(case_report_time, '%Y%m') = DATE_FORMAT(DATE_SUB(STR_TO_DATE(CONCAT(monthTable.months,'-01'),"%Y-%m-%d") ,INTERVAL 1 YEAR), '%Y%m')
			    AND case_area_code LIKE('150201%')
		) AS lastYearThisMonth,
		(
			SELECT 
			   COUNT(id) AS tCount
			FROM
			    case_report
			WHERE
			    case_area_code LIKE('150201%')
		) AS totalData
	FROM (SELECT 
		CONCAT('2022-', LPAD(id, 2, '0')) AS months	
	FROM
		a_query
	ORDER BY 
		id	 
	LIMIT 12
	) monthTable
) monthData