力扣数据库热题70--No.1193题解

官方难度中等，体感轻微繁琐，需要注意下小细节。

select left((trans_date),7) as month,country,
    count(*) as trans_count,
    count(case when state = 'approved' then 1 else null end) as approved_count,
    sum(amount) as trans_total_amount,
    sum(case when state = 'approved' then amount else 0 end) as approved_total_amount
from Transactions
group by left((trans_date),7),country

其他写法：

1）count带上if，起到和case when一样的作用（count里面是null，计0不计空）

COUNT(IF(state = 'approved', 1, NULL)) AS approved_count

2）date_format，可以按照指定格式转换日期（字母大写4位，小写2位）

DATE_FORMAT(trans_date, '%Y-%m')