Mysql-利用union实现全外连接，获取[本月,上月]数据进行对比

最新推荐文章于 2024-05-21 17:31:17 发布

笨鸟不是菜鸟

最新推荐文章于 2024-05-21 17:31:17 发布

阅读量1.2k

点赞数 1

分类专栏： MySql 文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/QIANG_HAO/article/details/104010870

版权

MySql 专栏收录该内容

9 篇文章 1 订阅

订阅专栏

收到业务需求需要分析各业务类型本月和上月的数据对比，查询数据的过程中主要需要处理一个问题：需要将本月、上月产生数据的业务类型全部显示，但有一部分上月产生数据的业务类型在本月还没有产生数据
效果图

将时间字段格式化为“2020-01”格式

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

为数据源添加“上月”时间标识 LAST_DATE_

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_

利用left join将“数据源”用时间和类型连接，获取当前时间业务数据及对应的上月业务数据

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_

为数据源添加“下月”时间标识 NEXT_DATE_

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_

利用left join将“数据源”用时间和类型连接，获取当前时间业务数据及对应的下月业务数据
注意：DATE_为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_

用union将“上月数据表”和“下月数据表”合并

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：因为mysql没有全外连接，所以需要用union代替

笨鸟不是菜鸟

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Mysql-利用union实现全外连接，获取[本月,上月]数据进行对比

收到业务需求需要分析各业务类型本月和上月的数据对比，查询数据的过程中主要需要处理一个问题：需要将本月、上月产生数据的业务类型全部显示，但有一部分上月产生数据的业务类型在本月还没有产生数据将时间字段格式化为“2020-01”格式DATE_FORMAT(CREATE_DATE_, '%Y-%m')为数据源添加“上月”时间标识SELECT DATE_FORMAT(CREATE_DA...
复制链接

扫一扫