MySQL实现把两行数据合并为一行

最新推荐文章于 2024-07-24 04:08:07 发布

lida_java

最新推荐文章于 2024-07-24 04:08:07 发布

阅读量8k

点赞数 1

分类专栏： Java MySQL

本文链接：https://blog.csdn.net/u013226462/article/details/51005330

版权

Java 同时被 2 个专栏收录

33 篇文章 0 订阅

订阅专栏

MySQL

6 篇文章 0 订阅

订阅专栏

在项目中遇到了这样一个问题：某人上月投资N元钱，本月投资M元钱，现要求把本月和上月的投资金额放在一行显示。如下图：

解决方案：用left join on

详细SQL脚本如下：

INSERT INTO DW_ADS.TR04_OPER_003 (
	BUSINESS_DATE,
	USER_ID,
	MONTH_INVEST_MONEY,
	LAST_MONTH_INVEST_MONEY,
	INVEST_NET,
	DATA_DATE
) SELECT
	DATE_FORMAT(
			LAST_DAY(
				DATE_FORMAT(IN_DATE, '%Y-%m-%d')
			),
			'%Y%m%d'
		),
	A.USER_ID,
	A.AMOUNT,
	B.AMOUNT,
	(A.AMOUNT - B.AMOUNT),
	IN_DATE
FROM
	(
		SELECT
			USER_ID,
			SUM(AMOUNT) AS AMOUNT
		FROM
			DW_BDS.SMY_BIDDING_TRANS_DAY
		WHERE
			BUSINESS_SYSTEM_DATE >= DATE_FORMAT(
				DATE_ADD(
					IN_DATE,
					INTERVAL - DAY (IN_DATE) + 1 DAY
				),
				'%Y%m%d'
			)
		AND BUSINESS_SYSTEM_DATE <= DATE_FORMAT(
			LAST_DAY(
				DATE_FORMAT(IN_DATE, '%Y-%m-%d')
			),
			'%Y%m%d'
		)
		GROUP BY
			USER_ID
	) AS A
LEFT JOIN (
	SELECT
		USER_ID,
		SUM(AMOUNT) AS AMOUNT
	FROM
		DW_BDS.SMY_BIDDING_TRANS_DAY
	WHERE
		BUSINESS_SYSTEM_DATE >= DATE_FORMAT(
			DATE_SUB(
				DATE_SUB(
					DATE_FORMAT(IN_DATE, '%y-%m-%d'),
					INTERVAL EXTRACT(DAY FROM IN_DATE) - 1 DAY
				),
				INTERVAL 1 MONTH
			),
			'%Y%m%d'
		)
	AND BUSINESS_SYSTEM_DATE <= DATE_FORMAT(
		DATE_SUB(
			DATE_SUB(
				DATE_FORMAT(IN_DATE, '%y-%m-%d'),
				INTERVAL extract(DAY FROM IN_DATE) DAY
			),
			INTERVAL 0 MONTH
		),
		'%Y%m%d'
	)
	GROUP BY
		USER_iD
) AS B ON A.USER_ID = B.USER_ID;