一段复杂的 SQL - 一洼绿地

按年月日分别统计成功的入账总计，外加一个待完成的总计

当发生支付时

INSERT INTO `amount_sum`
    (`user_id`, `business_type`, `last_date`, `year_sum`, `month_sum`, `day_sum`, `pending_sum`)
    VALUES
        (#{userId}, #{businessType}, DATE(#{date}), 0, 0, 0, ABS(${amount}))
    ON DUPLICATE KEY UPDATE
        # 输入年小于等于(不变)，输入年份大(0)
        `year_sum` = IF(
                YEAR(#{date}) &lt;= YEAR(`last_date`),
                `year_sum`,
                0
            ),
        # 输入月小于等于(不变)，输入月大(0)
        `month_sum` = IF(
                LAST_DAY(#{date}) &lt;= LAST_DAY(`last_date`),
                `month_sum`,
                0
            ),
        # 输入日小于等于(不变)，输入日大(0)
        `day_sum` = IF(
                DATE(#{date}) &lt;= DATE(`last_date`),
                `day_sum`,
                0
            ),
        # + pending amount
        `pending_sum` = `pending_sum` + ${amount},
        # last date 只能增加
        `last_date` = IF(
                DATE(#{date}) &lt; DATE(`last_date`),
                `last_date`,
                DATE(#{date})
            );

当最终支付成功后

UPDATE
    `amount_sum`
SET
    `last_date`=IF(
            DATE(#{date}) &lt; DATE(`last_date`),
            `last_date`,
            DATE(#{date})
        ),
    `year_sum`=IF(
            YEAR(`last_date`) = YEAR(#{date}),
            `year_sum` + ${amount},
            IF(YEAR(#{date}) &lt; YEAR(`last_date`), `year_sum`, ${amount})
        ),
    `month_sum`=IF(
            LAST_DAY(`last_date`) = LAST_DAY(#{date}),
            `month_sum` + ${amount},
            IF(LAST_DAY(#{date}) &lt; LAST_DAY(`last_date`), `month_sum`, ${amount})
        ),
    `day_sum`=IF(
            DATE(`last_date`) = DATE(#{date}),
            `day_sum` + ${amount},
            IF(DATE(#{date}) &lt; DATE(`last_date`), `day_sum`, ${amount})
        ),
    `pending_sum`=IF(
            `pending_sum` &gt; ${amount},
            `pending_sum` - ${amount},
            0
        )
WHERE
    `user_id`=#{userId}
    AND
    `business_type`=#{businessType};

当最终支付失败后

UPDATE
    `amount_sum`
SET
    `last_date`=IF(
            DATE(#{date}) &lt; DATE(`last_date`),
            `last_date`,
            DATE(#{date})
        ),
    `year_sum`=IF(
            YEAR(#{date}) &lt;= YEAR(`last_date`),
            `year_sum`,
            0
        ),
    `month_sum`=IF(
            LAST_DAY(#{date}) &lt;= LAST_DAY(`last_date`),
            `month_sum`,
            0
        ),
    `day_sum`=IF(
            DATE(#{date}) &lt;= DATE(`last_date`),
            `day_sum`,
            0
        ),
    `pending_sum`=IF(
            `pending_sum` &gt; ${amount},
            `pending_sum` - ${amount},
            0
        )
WHERE
    `user_id`=#{userId}
    AND
    `business_type`=#{businessType};