When receiving business requirements, it is necessary to analyze the data comparison between current month and last month for each business type. During data query, one problem needs to be solved: all business types of current month and last month need to be displayed, but some business types of last month have not yet generated data in this month

1. Format the time field as "2020-01"

DATE_FORMAT(CREATE_DATE_, '%Y-%m')

1. Add last month time id to data source

SELECT
	DATE_FORMAT(CREATE_DATE_, '%Y-%m') AS DATE_,
	DATE_FORMAT(date_sub(CREATE_DATE_,interval 1 MONTH), '%Y-%m') AS LAST_DATE_,
	a.MESSAGE_,
	COUNT(a.ID_) AS NUM_
FROM
	t_project_comment a
WHERE
	a.STATUS_ > 0
GROUP BY
	DATE_,
	MESSAGE_
ORDER BY
	DATE_,
	MESSAGE_

1. Use left join to connect "data source" with time and type to obtain current time business data and corresponding last month business data

SELECT
	a.DATE_,
	a.MESSAGE_,
	a.NUM_,
	a.LAST_DATE_,
	IFNULL(b.NUM_, 0) AS LAST_NUM_
FROM
	(
		SELECT
			DATE_FORMAT(CREATE_DATE_, '%Y-%m') AS DATE_,
			DATE_FORMAT(
				date_sub(
					CREATE_DATE_,
					INTERVAL 1 MONTH
				),
				'%Y-%m'
			) AS LAST_DATE_,
			a.MESSAGE_,
			COUNT(a.ID_) AS NUM_
		FROM
			t_project_comment a
		WHERE
			a.STATUS_ > 0
		GROUP BY
			DATE_,
			MESSAGE_
		ORDER BY
			DATE_,
			MESSAGE_
	) a
LEFT JOIN (
	SELECT
		DATE_FORMAT(CREATE_DATE_, '%Y-%m') AS DATE_,
		DATE_FORMAT(
			date_sub(
				CREATE_DATE_,
				INTERVAL 1 MONTH
			),
			'%Y-%m'
		) AS LAST_DATE_,
		a.MESSAGE_,
		COUNT(a.ID_) AS NUM_
	FROM
		t_project_comment a
	WHERE
		a.STATUS_ > 0
	GROUP BY
		DATE_,
		MESSAGE_
	ORDER BY
		DATE_,
		MESSAGE_
) b ON a.LAST_DATE_ = b.DATE_
AND a.MESSAGE_ = b.MESSAGE_

1. Add next month time id for data source

SELECT
	DATE_FORMAT(CREATE_DATE_, '%Y-%m') AS DATE_,
	DATE_FORMAT(date_add(CREATE_DATE_,interval 1 MONTH), '%Y-%m') AS NEXT_DATE_,
	a.MESSAGE_,
	COUNT(a.ID_) AS NUM_
FROM
	t_project_comment a
WHERE
	a.STATUS_ > 0
GROUP BY
	DATE_,
	MESSAGE_
ORDER BY
	DATE_,
	MESSAGE_

1. Use left join to connect "data source" with time and type to obtain business data of current time and corresponding business data of next month
   Note: DATE is next DATE_

SELECT
	a.NEXT_DATE_ AS DATE_,
	a.MESSAGE_,
	IFNULL(b.NUM_, 0) AS NUM_,
	a.DATE_ AS LAST_DATE_,
	IFNULL(a.NUM_, 0) AS LAST_NUM_
FROM
	(
		SELECT
			DATE_FORMAT(CREATE_DATE_, '%Y-%m') AS DATE_,
			DATE_FORMAT(
				date_add(
					CREATE_DATE_,
					INTERVAL 1 MONTH
				),
				'%Y-%m'
			) AS NEXT_DATE_,
			a.MESSAGE_,
			COUNT(a.ID_) AS NUM_
		FROM
			t_project_comment a
		WHERE
			a.STATUS_ > 0
		GROUP BY
			DATE_,
			MESSAGE_
		ORDER BY
			DATE_,
			MESSAGE_
	) a
LEFT JOIN (
	SELECT
		DATE_FORMAT(CREATE_DATE_, '%Y-%m') AS DATE_,
		DATE_FORMAT(
			date_add(
				CREATE_DATE_,
				INTERVAL 1 MONTH
			),
			'%Y-%m'
		) AS NEXT_DATE_,
		a.MESSAGE_,
		COUNT(a.ID_) AS NUM_
	FROM
		t_project_comment a
	WHERE
		a.STATUS_ > 0
	GROUP BY
		DATE_,
		MESSAGE_
	ORDER BY
		DATE_,
		MESSAGE_
) b ON a.NEXT_DATE_ = b.DATE_
AND a.MESSAGE_ = b.MESSAGE_

1. Use union to merge last month data table and next month data table

SELECT
	a.DATE_,
	a.MESSAGE_,
	a.NUM_,
	a.LAST_DATE_,
	IFNULL(b.NUM_, 0) AS LAST_NUM_
FROM
	(
		SELECT
			DATE_FORMAT(CREATE_DATE_, '%Y-%m') AS DATE_,
			DATE_FORMAT(
				date_sub(
					CREATE_DATE_,
					INTERVAL 1 MONTH
				),
				'%Y-%m'
			) AS LAST_DATE_,
			a.MESSAGE_,
			COUNT(a.ID_) AS NUM_
		FROM
			t_project_comment a
		WHERE
			a.STATUS_ > 0
		GROUP BY
			DATE_,
			MESSAGE_
		ORDER BY
			DATE_,
			MESSAGE_
	) a
LEFT JOIN (
	SELECT
		DATE_FORMAT(CREATE_DATE_, '%Y-%m') AS DATE_,
		DATE_FORMAT(
			date_sub(
				CREATE_DATE_,
				INTERVAL 1 MONTH
			),
			'%Y-%m'
		) AS LAST_DATE_,
		a.MESSAGE_,
		COUNT(a.ID_) AS NUM_
	FROM
		t_project_comment a
	WHERE
		a.STATUS_ > 0
	GROUP BY
		DATE_,
		MESSAGE_
	ORDER BY
		DATE_,
		MESSAGE_
) b ON a.LAST_DATE_ = b.DATE_
AND a.MESSAGE_ = b.MESSAGE_
UNION
	SELECT
		a.NEXT_DATE_ AS DATE_,
		a.MESSAGE_,
		IFNULL(b.NUM_, 0) AS NUM_,
		a.DATE_ AS LAST_DATE_,
		IFNULL(a.NUM_, 0) AS LAST_NUM_
	FROM
		(
			SELECT
				DATE_FORMAT(CREATE_DATE_, '%Y-%m') AS DATE_,
				DATE_FORMAT(
					date_add(
						CREATE_DATE_,
						INTERVAL 1 MONTH
					),
					'%Y-%m'
				) AS NEXT_DATE_,
				a.MESSAGE_,
				COUNT(a.ID_) AS NUM_
			FROM
				t_project_comment a
			WHERE
				a.STATUS_ > 0
			GROUP BY
				DATE_,
				MESSAGE_
			ORDER BY
				DATE_,
				MESSAGE_
		) a
	LEFT JOIN (
		SELECT
			DATE_FORMAT(CREATE_DATE_, '%Y-%m') AS DATE_,
			DATE_FORMAT(
				date_add(
					CREATE_DATE_,
					INTERVAL 1 MONTH
				),
				'%Y-%m'
			) AS NEXT_DATE_,
			a.MESSAGE_,
			COUNT(a.ID_) AS NUM_
		FROM
			t_project_comment a
		WHERE
			a.STATUS_ > 0
		GROUP BY
			DATE_,
			MESSAGE_
		ORDER BY
			DATE_,
			MESSAGE_
	) b ON a.NEXT_DATE_ = b.DATE_
	AND a.MESSAGE_ = b.MESSAGE_

Ps: because mysql has no external connection, you need to use union instead