1193 每月的交易 leetcode 数据库刷题（自己写出）

1193 每月的交易
SQL 架构：

create table if not exists Transactions (id int, country varchar(4), state enum('approved', 'declined'), amount int, trans_date date)
Truncate table Transactions
insert into Transactions (id, country, state, amount, trans_date) values ('121', 'US', 'approved', '1000', '2018-12-18')
insert into Transactions (id, country, state, amount, trans_date) values ('122', 'US', 'declined', '2000', '2018-12-19')
insert into Transactions (id, country, state, amount, trans_date) values ('123', 'US', 'approved', '2000', '2019-01-01')
insert into Transactions (id, country, state, amount, trans_date) values ('124', 'DE', 'approved', '2000', '2019-01-07')

select  a.month ,a.country,count(a.id) as 'trans_count',
#之前一直学习的sum 用法
sum(if(a.state = 'approved',1,0)) as 'approved_count',
sum(amount) as 'trans_total_amount',
sum(if(a.state = 'approved',a.amount,0)) as 'approved_total_amount'
from 
#sunstr 对月份进行切片
(select  id,country,state , amount, substr(trans_date,1,7) as 'month'
from   Transactions)a
#不同月份 ，不同地区/国家
group by a.month,a.country