本文共 4195 字，大约阅读时间需要 13 分钟。

1. 题目

Transactions 记录表

+----------------+---------+| Column Name    | Type    |+----------------+---------+| id             | int     || country        | varchar || state          | enum    || amount         | int     || trans_date     | date    |+----------------+---------+id 是这个表的主键。该表包含有关传入事务的信息。状态列是类型为 [approved（已批准）、declined（已拒绝）] 的枚举。

Chargebacks 表

+----------------+---------+| Column Name    | Type    |+----------------+---------+| trans_id       | int     || charge_date    | date    |+----------------+---------+退单包含有关放置在事务表中的某些事务的传入退单的基本信息。trans_id 是 transactions 表的 id 列的外键。每项退单都对应于之前进行的交易，即使未经批准。

编写一个 SQL 查询，以查找每个月和每个国家/地区的已批准交易的数量及其总金额、退单的数量及其总金额。

注意：在您的查询中，给定月份和国家，忽略所有为零的行。

查询结果格式如下所示：

Transactions 表：+------+---------+----------+--------+------------+| id   | country | state    | amount | trans_date |+------+---------+----------+--------+------------+| 101  | US      | approved | 1000   | 2019-05-18 || 102  | US      | declined | 2000   | 2019-05-19 || 103  | US      | approved | 3000   | 2019-06-10 || 104  | US      | declined | 4000   | 2019-06-13 || 105  | US      | approved | 5000   | 2019-06-15 |+------+---------+----------+--------+------------+Chargebacks 表：+------------+------------+| trans_id   | trans_date |+------------+------------+| 102        | 2019-05-29 || 101        | 2019-06-30 || 105        | 2019-09-18 |+------------+------------+Result 表：+----------+---------+----------------+-----------------+-------------------+--------------------+| month    | country | approved_count | approved_amount | chargeback_count  | chargeback_amount  |+----------+---------+----------------+-----------------+-------------------+--------------------+| 2019-05  | US      | 1              | 1000            | 1                 | 2000               || 2019-06  | US      | 2              | 8000            | 1                 | 1000               || 2019-09  | US      | 0              | 0               | 1                 | 5000               |+----------+---------+----------------+-----------------+-------------------+--------------------+

来源：力扣（LeetCode）

链接：https://leetcode-cn.com/problems/monthly-transactions-ii

著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

2. 解题

# Write your MySQL query statement belowselect *from(    select t.month, t.country,             ifnull(sum(t1.approved_count),0) approved_count,             ifnull(sum(t1.approved_amount),0) approved_amount,            ifnull(sum(t2.chargeback_count),0) chargeback_count,             ifnull(sum(t2.chargeback_amount),0) chargeback_amount    from     (        select distinct country, date_format(trans_date, '%Y-%m') month        from Transactions        union        select distinct country, date_format(ch.trans_date, '%Y-%m') month        from Chargebacks ch left join Transactions tr        on ch.trans_id = tr.id                ) t    left join    (        select date_format(trans_date, '%Y-%m') month,             country,             count(*) approved_count,            ifnull(sum(amount),0) approved_amount        from Transactions        where state='approved'        group by month, country    ) t1    on t.month = t1.month and t.country = t1.country    left join     (        select date_format(ch.trans_date, '%Y-%m') month,        country,        count(*) chargeback_count,        ifnull(sum(amount),0) chargeback_amount        from Chargebacks ch left join Transactions tr        on ch.trans_id = tr.id        group by month, country    ) t2    on t.month = t2.month and t.country = t2.country    group by month, country) tmpwhere tmp.approved_count != 0 or tmp.chargeback_count != 0

or 简单写法，创建一个 chargeback state

select date_format(a.trans_date,'%Y-%m') month,country,       sum(state = 'approved') approved_count,       sum(if(state = 'approved',amount,0)) approved_amount,       sum(state = 'chargeback') chargeback_count,       sum(if(state = 'chargeback',amount,0)) chargeback_amount  from(    select *     from transactions    where state = 'approved'        union all    select id, country, 'chargeback' state, amount, c.trans_date    from chargebacks c left join transactions t     on c.trans_id = t.id) agroup by month,country

我的CSDN

长按或扫码关注我的公众号（Michael阿明），一起加油、一起学习进步！

转载地址：https://michael.blog.csdn.net/article/details/107663011 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！