LeetCode-SQL-1205. 每月交易II

# Write your MySQL query statement below

SELECT month,country,
       SUM(IF(type = 'approved', 1, 0)) AS approved_count,
       SUM(IF(type = 'approved', amount, 0)) AS approved_amount,
       SUM(IF(type = 'chargeback', 1, 0)) AS chargeback_count,
       SUM(IF(type = 'chargeback', amount, 0)) AS chargeback_amount
FROM (
        (SELECT date_format(t.trans_date,'%Y-%m') AS month,
                t.country,
                amount,
                'approved' AS type
         FROM Transactions AS t
         WHERE state = 'approved' )
      UNION ALL
        (SELECT date_format(c.trans_date,'%Y-%m') AS month,
                t1.country,
                amount,
                'chargeback' AS type
         FROM Transactions AS t1
         JOIN Chargebacks AS c ON t1.id = c.trans_id)) AS t2
GROUP BY t2.month,
         t2.country;

 

 
发布了231 篇原创文章 · 获赞 4 · 访问量 2万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览