力扣Mysql热题五十复盘2

Problem: 1193. 每月交易 I思路date_format()的应用count()与sum()中条件判断的应用解题过程难点一date_format(日期字段,%Y-%m)将日期格式化成指定字符串,此处只展示年月日相关应用年%Y——2026 %y——26月%M——March %m——03 %c——3日%d——05 %e——5难点二count(case when 条件 then 1 else 0/null end)统计结果与count()的底层逻辑有关count会统计0对应的行但不会统计null对应的行例如a不符合条件返回0count最终依旧会把a算上b不符合条件返回null则count不会把b算上sumcase when 条件 then 字段 else 0 end条件判断通过后sum会统计字段对应的数值此时若写成then 1sum最终只会返回1Code# Write your MySQL query statement below select date_format(trans_date,%Y-%m) as month, country, count(id) as trans_count, count(case when stateapproved then 1 else null end) as approved_count, sum(amount) as trans_total_amount, sum(case when stateapproved then amount else 0 end) as approved_total_amount from Transactions group by month,country;