在项目中遇到了这样一个问题:某人上月投资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_FO