1205. 每月交易II

SQL架构

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

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

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

以 任意顺序 返回结果表。

查询结果格式如下所示。

示例 1:

输入：
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 |
+----------+------------+
输出：
+---------+---------+----------------+-----------------+------------------+-------------------+
| 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              |
+---------+---------+----------------+-----------------+------------------+-------------------+

select   # 用 sum 得到 union all 后的 已批准交易的数量及其总金额、退单的数量及其总金额。
sss1.month,sss1.country,sum(sss1.approved_count) approved_count,sum(sss1.approved_amount) approved_amount,sum(sss1.chargeback_count) chargeback_count,sum(sss1.chargeback_amount) chargeback_amount
from
(
(
select  # 得到已批准交易的数量及其总金额
date_format(t.trans_date,'%Y-%m') `month`,t.country,sum(if(t.state = 'approved',1,0)) approved_count,sum(if(t.state = 'approved',t.amount,0)) approved_amount,0 chargeback_count,0 chargeback_amount  # 常量0 是为了保证上下两个表列的的个数一直 否者 不能用 union all
from
Transactions t 
group by
date_format(t.trans_date,'%Y-%m'),t.country
) 

union all

(
select  # 得到退单的数量及其总金额
date_format(c.trans_date,'%Y-%m') `month`,t.country,0 approved_count,0 approved_amount,count(c.trans_id) chargeback_count,sum(amount)chargeback_amount   # 常量0 是为了保证上下两个表列的的个数一直 否者 不能用 union all
from
Chargebacks c left join Transactions t
on 
c.trans_id = t.id
group by
date_format(c.trans_date,'%Y-%m'),t.country
) 
)sss1
where sss1.approved_count !=0 or sss1.approved_amount !=0 or sss1.chargeback_count !=0 or sss1.chargeback_amount !=0
group by sss1.month,sss1.country

笔记:单用 T表 或者 C表 都会使 显示给定月份 不全
顾必须用 union all