1. 题目描述

Transactions表:

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

id 是这个表的主键 该表包含有关传入事务的信息 state 列类型为 ["approved", "declined"] 之一 编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额, 已批准的事务数及其总金额 以 任意顺序 返回结果表

2. 测试用例

输入: 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 |
+------+---------+----------+--------+------------+

输出:

+----------+---------+-------------+----------------+--------------------+-----------------------+
| 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                  |
+----------+---------+-------------+----------------+--------------------+-----------------------+

3. 解题思路

1. 按照trans_date和country分组统计数据, 使用DATE_FORMAT()对trans_date做日期截取获得年和月

select DATE_FORMAT(trans_date, '%Y-%m')       as month,
       country,
       count(*)                               as trans_count,
       sum(if(state = 'approved', 1, 0))      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;

• DATE_FORMAT(trans_date, '%Y-%m') 是对时间截取获取年和月
• um(if(state = 'approved', amount, 0)) as approved_total_amount 只计算状态为approved的数据

查询结果

+-------+-------+-----------+--------------+------------------+---------------------+
|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                 |
+-------+-------+-----------+--------------+------------------+---------------------+