我是一名立志把细节说清楚的博主,欢迎【关注】🎉 ~
原创不易, 如果有帮助 ,记得【点赞】【收藏】 哦~ ❥(^_-)~
如有错误、疑惑,欢迎【评论】指正探讨,我会尽可能第一时间回复的,谢谢支持
原题
表:Transactions
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| country | varchar |
| state | enum |
| amount | int |
| trans_date | date |
+---------------+---------+
id
是这个表的主键。
该表包含有关传入事务的信息。
state
列类型为 [“approved”, “declined”] 之一。
编写一个 sql
查询来查找 每个月 和 每个国家/地区 的 事务数 及其 总金额、已批准的事务数 及其 总金额。
以 任意顺序 返回结果表。
查询结果格式如下所示。
示例 1:
输入:
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 |
+----------+---------+-------------+----------------+--------------------+-----------------------+
题解
SELECT DATE_FORMAT(trans_date, '%Y-%m') AS month,
country,
COUNT(*) 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
解题笔记
- 查找每个月和每个国家/地区。
DATE_FORMAT()
函数,转换日期格式。将原先精确到日的日期,转换为精确到月的日期。DATE_FORMAT(trans_date, '%Y-%m')
GROUP BY
关键字,按月份和国家进行分组。GROUP BY month, country
- 第一步已经将数据按月和国家聚合,只需要使用
COUNT()
函数就能获取到总的事务数。COUNT(*) AS trans_count
- 使用 SUM 函数计算总金额。
SUM(amount) AS trans_total_amount
- 查找已批准的事物数。
已批准的事物的 state 标记为approved
。首先使用IF
函数将state = 'approved'
的记录标记为 1,否则为NULL
。再使用COUNT
计算总量。- 数值为1的就会被
COUNT()
函数记录。数值为NULL
的不会被记录。
COUNT(IF(state = 'approved', 1, NULL)) AS approved_count
- 数值为1的就会被
- 查找已批准的事物的总金额。
和第四步一样,先使用IF
函数,再使用SUM
函数。- 和上面不同的是,最外层不是
COUNT
函数,所以为了符合SUM
函数加法的运算规律,将不符合条件的数值设置为 0,而不是NULL。
SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount
- 和上面不同的是,最外层不是
我是一名立志把细节说清楚的博主,欢迎【关注】🎉 ~
原创不易, 如果有帮助 ,记得【点赞】【收藏】 哦~ ❥(^_-)~
如有错误、疑惑 ,欢迎【评论】指正探讨,我会尽可能第一时间回复的,谢谢支持