MYSQL按月對收款金額進行統計

需求說明：

有支付記錄表和支付類型兩個表，按支付類型分組，統計每月的支付金額。做記錄以便以後檢視和學習

表結構

1、先統計單個月不同支付方式的收到的總金額

執行結果如下（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 (dece.pay_type=a.id)
group by a.title;
           

執行結果如下（12月每種類型的收款金額則展示出來）：

3、1月到12月同理照此方法寫，代碼大緻如下：

select a.title as '支付類型',
(case when jan.moneys is null then 0 else jan.moneys end) as '1月',
(case when feb.moneys is null then 0 else feb.moneys end) as '2月',
......
(case when nov.moneys is null then 0 else nov.moneys end) as '11月',
(case when dece.moneys is null then 0 else dece.moneys end) as '12月',
(case when oneyear.moneys is null then 0 else oneyear.moneys end) as '年統計'
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)='1' and year(b.time)=year(curdate()) group by b.pay_type) as jan on (jan.pay_type=a.id)
left join (select b.pay_type,sum(b.pay_money) as moneys from cmf_paylog as b
where month(b.time)='2' and year(b.time)=year(curdate()) group by b.pay_type) as feb on (feb.pay_type=a.id)
......
where month(b.time)='11' and year(b.time)=year(curdate()) group by b.pay_type) as nov on (nov.pay_type=a.id)
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 (dece.pay_type=a.id)
left join (select b.pay_type,sum(b.pay_money) as moneys from cmf_paylog as b
where year(b.time)=year(curdate()) group by b.pay_type) as oneyear on (oneyear.pay_type=a.id)
group by a.title;
           

4、執行結果：

5、如果要橫豎向調換則重組數組（PHP語言）：

$data = Db::query($sql);//上面的查詢結果
        //随便找的某一鍵值，組成'月'用
        $key = array_keys($data[0]);//3個
        for($i=0;$i<count($key)-1;$i++){
            $datas[$i]['yue'] = $key[$i+1];
            foreach($data as $k=>$v){
                $datas[$i][$v['paytype']] = $v[$key[$i+1]];
            }
        }
        dump($datas);die;
           

列印結果：