题目： 分析：该题分了两种情况，分别是approved，和 chargebasks的情况，而且出现了两个日期，因此直接采用join是行不通的，只能用合并的方式。结果中的年月用date_format(date,format)实现。
解法：
查出满足state='approved’条件的各类信息。 select country,state,amount,date_format(trans_date,"%Y-%m") as month,1 as tag from Transactions where state='approved' 再查出回退单子(chargebasks)的信息，此处需要right join chargebasks表,然后跟上面的union all合并。 select country,state,amount,date_format(c.trans_date,"%Y-%m") as month,0 as tag from Transactions t right join Chargebacks c on c.trans_id=t.id 最后用count，sum根据条件聚合，再根据country,month分组。▲这里使用tag是为了更好的区分两种不同情况
解：
select month, country, count(case when state='approved' and tag=1 then 1 end) as approved_count, sum(case when tag=1 then amount else 0 end) as approved_amount, count(case when tag=0 then 1 end) as chargeback_count, sum(case when tag=0 then amount else 0 end) as chargeback_amount from( select country,state,amount,date_format(trans_date,"%Y-%m") as month,1 as tag from Transactions where state='approved' union all select country,state,amount,date_format(c.trans_date,"%Y-%m") as month,0 as tag from Transactions t right join Chargebacks c on c.trans_id=t.id ) as a group by month,country;