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

曾经终败给现在 2022-08-30 00:55 121阅读 0赞

**Table: Transactions**

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>country</td> 
   <td>varchar</td> 
  </tr> 
  <tr> 
   <td>state</td> 
   <td>enum</td> 
  </tr> 
  <tr> 
   <td>amount</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>trans_date</td> 
   <td>date</td> 
  </tr> 
 </tbody> 
</table>

id 是这个表的主键。  
该表包含有关传入事务的信息。  
状态列是类型为 \[approved（已批准）、declined（已拒绝）\] 的枚举。

**Chargebacks 表**

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>trans_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>trans_date</td> 
   <td>date</td> 
  </tr> 
 </tbody> 
</table>

退单包含有关放置在事务表中的某些事务的传入退单的基本信息。  
trans\_id 是 transactions 表的 id 列的外键。  
每项退单都对应于之前进行的交易，即使未经批准。

### 问题 ###

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

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

### 示例 ###

**Transactions 表：**

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>country</th> 
   <th>state</th> 
   <th>amount</th> 
   <th>trans_date</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>101</td> 
   <td>US</td> 
   <td>approved</td> 
   <td>1000</td> 
   <td>2019-05-18</td> 
  </tr> 
  <tr> 
   <td>102</td> 
   <td>US</td> 
   <td>declined</td> 
   <td>2000</td> 
   <td>2019-05-19</td> 
  </tr> 
  <tr> 
   <td>103</td> 
   <td>US</td> 
   <td>approved</td> 
   <td>3000</td> 
   <td>2019-06-10</td> 
  </tr> 
  <tr> 
   <td>104</td> 
   <td>US</td> 
   <td>declined</td> 
   <td>4000</td> 
   <td>2019-06-13</td> 
  </tr> 
  <tr> 
   <td>105</td> 
   <td>US</td> 
   <td>approved</td> 
   <td>5000</td> 
   <td>2019-06-15</td> 
  </tr> 
 </tbody> 
</table>

**Chargebacks 表：**

<table> 
 <thead> 
  <tr> 
   <th>trans_id</th> 
   <th>trans_date</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>102</td> 
   <td>2019-05-29</td> 
  </tr> 
  <tr> 
   <td>101</td> 
   <td>2019-06-30</td> 
  </tr> 
  <tr> 
   <td>105</td> 
   <td>2019-09-18</td> 
  </tr> 
 </tbody> 
</table>

**Result 表：**

<table> 
 <thead> 
  <tr> 
   <th>month</th> 
   <th>country</th> 
   <th>approved_count</th> 
   <th>approved_amount</th> 
   <th>chargeback_count</th> 
   <th>chargeback_amount</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>2019-05</td> 
   <td>US</td> 
   <td>1</td> 
   <td>1000</td> 
   <td>1</td> 
   <td>2000</td> 
  </tr> 
  <tr> 
   <td>2019-06</td> 
   <td>US</td> 
   <td>2</td> 
   <td>8000</td> 
   <td>1</td> 
   <td>1000</td> 
  </tr> 
  <tr> 
   <td>2019-09</td> 
   <td>US</td> 
   <td>0</td> 
   <td>0</td> 
   <td>1</td> 
   <td>5000</td> 
  </tr> 
 </tbody> 
</table>

来源：力扣（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