题41:
根据下表编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。
其中:
- id是这个表的主键;
- 此表包含有关传入事务的信息;
- state列类型为"[“批准”,“拒绝”]之一。
解题思路:
(1)查找每个月和每个国家/地区,由于表中的日期精确到日,因此利用date_format()函数将日期按年月输入,再获取所有的月份后,使用GROUP BY聚合每个月和每个国家的记录;
(2)查找总的事物数,只需要使用count()函数就能获取到总的事务数;
(3)查找总金额,使用sum()函数计算总金额;
(4)查找已批准的事物数,先使用if()函数将state ='approved’的记录标记为1,否则为NULL,然后使用count()计算总量;
(5)查找已批准的事物的总金额,与第(4)相同。
具体代码如下:
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;