题目来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/monthly-transactions-i
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
本人思路:
– 此题注意count()/sum()函数在聚合时可以加入条件
– sum()函数中返回0/count()函数中返回null 可以过滤掉不符合记录
select date_format(trans_date,"%Y-%m") as month,country,
count(id) 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