LeetCode（数据库）- 每月交易II

骑猪看日落 2022-09-09 13:49 165阅读 0赞

**题目链接：**[点击打开链接][Link 1]

**题目大意：**略。

**解题思路：**先将退单表 union all 到交易表，无非是把 state 标记为一个新的状态，比如 cancel，这样两张表可以合并操作，思路就清晰很多。

第二个案例为什么二月份 CB 会没有？

因为此时此地 approved 和 charge 都没有，所以根据题目要求需要过滤掉。

**AC 代码**

-- 解决方案(1)
    WITH t1 AS(SELECT * FROM Transactions
    
    UNION ALL
    
    SELECT trans_id, country, 'cancel' state, amount, c.trans_date
    FROM Chargebacks c JOIN Transactions t
    ON c.trans_id = t.id),
    
    t2 AS(SELECT DATE_FORMAT(trans_date, '%Y-%m') month, 
    country, 
    COUNT(IF(state = 'approved', 1, null)) approved_count,
    SUM(IF(state = 'approved', amount, 0)) approved_amount,
    COUNT(IF(state = 'cancel', 1, null)) chargeback_count,
    SUM(IF(state = 'cancel', amount, 0)) chargeback_amount
    FROM t1
    GROUP BY month, country)
    
    SELECT *
    FROM t2
    WHERE !(approved_count = 0 AND chargeback_count = 0)
    
    -- 解决方案(2)
    SELECT month, country,
    COUNT(IF(tag=1, 1, NULL)) AS approved_count,
    SUM(IF(tag=1, amount, 0)) AS approved_amount,
    COUNT(IF(tag=0, 1, NULL)) AS chargeback_count,
    SUM(IF(tag=0, amount, 0)) AS chargeback_amount
    FROM (
        SELECT country, amount, 1 AS tag,
        date_format(trans_date, '%Y-%m') AS month
        FROM Transactions
        WHERE state='approved'
        
        UNION ALL
    
        SELECT country, amount, 0 AS tag,
        date_format(c.trans_date, '%Y-%m') AS month
        FROM Transactions AS t RIGHT OUTER JOIN Chargebacks AS c
        ON t.id = c.trans_id
    ) AS temp
    GROUP BY  month, country;

[Link 1]: https://leetcode-cn.com/problems/monthly-transactions-ii/