MYSQL按月对收款金额进行统计

需求说明：

有支付记录表和支付类型两个表，按支付类型分组，统计每月的支付金额。做记录以便以后查看和学习

表结构

1、先统计单个月不同支付方式的收到的总金额

select pay_type,sum(pay_money) as moneys from cmf_paylog where month(time)='12' and year(time)=year(curdate()) group by pay_type;

执行结果如下（12月各种支付方式的收款金额）：

2、把它与支付类型表连接

select a.title as '支付类型',
(case when dece.moneys is null then 0 else dece.moneys end) as '12月'
from cmf_paytype as a
left join (select b.pay_type,sum(b.pay_money) as moneys from cmf_paylog as b
where month(b.time)='12' and year(b.time)=year(curdate()) group by b.pay_type) as dece on