Medium之1193.每月交易I

秒速五厘米 2023-01-23 15:00 75阅读 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 是这个表的主键。  
该表包含有关传入事务的信息。  
state 列类型为 “\[”批准“，”拒绝“\] 之一。

### 问题 ###

编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。

### 示例 ###

查询结果格式如下所示：

**Transactions table:**

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>country</th> 
   <th>state</th> 
   <th>amount</th> 
   <th>trans_date</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>121</td> 
   <td>US</td> 
   <td>approved</td> 
   <td>1000</td> 
   <td>2018-12-18</td> 
  </tr> 
  <tr> 
   <td>122</td> 
   <td>US</td> 
   <td>declined</td> 
   <td>2000</td> 
   <td>2018-12-19</td> 
  </tr> 
  <tr> 
   <td>123</td> 
   <td>US</td> 
   <td>approved</td> 
   <td>2000</td> 
   <td>2019-01-01</td> 
  </tr> 
  <tr> 
   <td>124</td> 
   <td>DE</td> 
   <td>approved</td> 
   <td>2000</td> 
   <td>2019-01-07</td> 
  </tr> 
 </tbody> 
</table>

**Result table:**

<table> 
 <thead> 
  <tr> 
   <th>month</th> 
   <th>country</th> 
   <th>trans_count</th> 
   <th>approved_count</th> 
   <th>trans_total_amount</th> 
   <th>approved_total_amount</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>2018-12</td> 
   <td>US</td> 
   <td>2</td> 
   <td>1</td> 
   <td>3000</td> 
   <td>1000</td> 
  </tr> 
  <tr> 
   <td>2019-01</td> 
   <td>US</td> 
   <td>1</td> 
   <td>1</td> 
   <td>2000</td> 
   <td>2000</td> 
  </tr> 
  <tr> 
   <td>2019-01</td> 
   <td>DE</td> 
   <td>1</td> 
   <td>1</td> 
   <td>2000</td> 
   <td>2000</td> 
  </tr> 
 </tbody> 
</table>

来源：力扣（LeetCode）  
链接：https://leetcode-cn.com/problems/monthly-transactions-i  
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

### 解答 ###

select left(trans_date,7) as month,
           country,
           count(state) as trans_count,
           count(if(state='approved',1,null)) as approved_count,
           sum(amount) as trans_total_amount,
           sum(if(state='approved',amount,0)) as approved_total_amount
    from transactions
    group by month,country

**1.查找已批准的事物数：**  
已批准的事物的 state 标记为 approved。首先使用 IF 函数将 state = ‘approved’ 的记录标记为 1，否则为 NULL。再使用 COUNT 计算总量。  
（count计数忽略null）

**2.查找已批准的事物的总金额：**  
和第四步一样，先使用 IF 函数，再使用 SUM 函数。

**3.每个月的表示：**  
（1）使用 DATE\_FORMAT() 函数将日期按照年月 %Y-%m 输出。比如将 2019-01-02 转换成 2019-01 。月份一定要是m，而不能是M，因为m表示01，02，…12，而M表示月份为1,2,…12。  
DATE\_FORMAT(trans\_date, ‘%Y-%m’)  
（2）也可以left(trans\_date,7)