Medium之1205.每月交易II***

Table: 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 查询，以查找每个月和每个国家/地区的信息：已批准交易的数量及其总金额、退单的数量及其总金额。

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

示例

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
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

解答

select left(trans_date,7) as month,
       country,
       count(if(state = 'approved',1,null)) as approved_count,
       sum(if(state = 'approved',amount,0)) as approved_amount,
       sum(state='chargeback') as chargeback_count,
       sum(if(state = 'chargeback',amount,0)) as chargeback_amount
from(
    select * from transactions
    union all
    select id,country,'chargeback' as state,amount,c.trans_date
    from transactions t 
         right join 
         chargebacks c on c.trans_id = t.id  
    ) tmp
group by month,country
having approved_amount or chargeback_amount

知识点

1.union all：考虑点：两个表的时间不是完全对应或者完全归属的，如果不使用union all会出现漏掉日期字段的情况。

2.having approved_amount or chargeback_amount
：用于保证approved_amount或者chargeback_amount至少有一个不为0（基于题目中条件：只需显示给定月份和国家，忽略所有为零的行）。

3.count(if(state = ‘approved’,1,null)) = sum(state = ‘approved’)
因为sum(条件)：对符合条件的结果行数进行求和

4.提取月份
date_format(trans_date, ‘%Y-%m’) month
或者left(trans_date,7) as month