Medium之1193.每月交易I

Table: Transactions

Column Name	Type
id	int
country	varchar
state	enum
amount	int
trans_date	date

id 是这个表的主键。
该表包含有关传入事务的信息。
state 列类型为 “[”批准“，”拒绝“] 之一。

问题

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

示例

查询结果格式如下所示：

Transactions table:

id	country	state	amount	trans_date
121	US	approved	1000	2018-12-18
122	US	declined	2000	2018-12-19
123	US	approved	2000	2019-01-01
124	DE	approved	2000	2019-01-07

Result table:

month	country	trans_count	approved_count	trans_total_amount	approved_total_amount
2018-12	US	2	1	3000	1000
2019-01	US	1	1	2000	2000
2019-01	DE	1	1	2000	2000

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