mysql语句优化

这是年中详细月查询

select DATE_FORMAT(c.orderDate,'%Y') as '年份',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 1  then c.totalAmount else  0 end  ) as '一月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 2  then c.totalAmount else  0 end  ) as '二月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 3  then c.totalAmount else  0 end  ) as '三月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 4  then c.totalAmount else  0 end  ) as '四月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 5  then c.totalAmount else  0 end  ) as '五月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 6  then c.totalAmount else  0 end  ) as '六月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 7  then c.totalAmount else  0 end  ) as '七月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 8  then c.totalAmount else  0 end  ) as '八月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 9  then c.totalAmount else  0 end  ) as '九月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 10  then c.totalAmount else  0 end  ) as '十月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 11  then c.totalAmount else  0 end  ) as '十一月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 12  then c.totalAmount else  0 end  ) as '十二月',sum(c.totalAmount) as '总金额' FROM erp$sales$sales_order c LEFT JOIN erp$base$customer k ON c.customer_id=k.idLEFT JOIN erp$base$customer_category f  ON  k.customer_category_id =f.id LEFT JOIN erp$base$area a ON  c.area_id=a.idwhere DATE_FORMAT(c.orderDate,'%Y')=2015UNION ALL select DATE_FORMAT(c.orderDate,'%Y') as '年份',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 1  then c.totalAmount else  0 end  ) as '一月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 2  then c.totalAmount else  0 end  ) as '二月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 3  then c.totalAmount else  0 end  ) as '三月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 4  then c.totalAmount else  0 end  ) as '四月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 5  then c.totalAmount else  0 end  ) as '五月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 6  then c.totalAmount else  0 end  ) as '六月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 7  then c.totalAmount else  0 end  ) as '七月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 8  then c.totalAmount else  0 end  ) as '八月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 9  then c.totalAmount else  0 end  ) as '九月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 10  then c.totalAmount else  0 end  ) as '十月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 11  then c.totalAmount else  0 end  ) as '十一月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 12  then c.totalAmount else  0 end  ) as '十二月',sum(c.totalAmount) as '总金额' FROM erp$sales$sales_order c LEFT JOIN erp$base$customer k ON c.customer_id=k.idLEFT JOIN erp$base$customer_category f  ON  k.customer_category_id =f.id LEFT JOIN erp$base$area a ON  c.area_id=a.idwhere DATE_FORMAT(c.orderDate,'%Y')=2014

还有别一种更好

select DATE_FORMAT(c.orderDate,'%Y') as '年份',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 1  then c.totalAmount else  0 end  ) as '一月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 2  then c.totalAmount else  0 end  ) as '二月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 3  then c.totalAmount else  0 end  ) as '三月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 4  then c.totalAmount else  0 end  ) as '四月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 5  then c.totalAmount else  0 end  ) as '五月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 6  then c.totalAmount else  0 end  ) as '六月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 7  then c.totalAmount else  0 end  ) as '七月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 8  then c.totalAmount else  0 end  ) as '八月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 9  then c.totalAmount else  0 end  ) as '九月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 10  then c.totalAmount else  0 end  ) as '十月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 11  then c.totalAmount else  0 end  ) as '十一月',sum( case  DATE_FORMAT(c.orderDate,'%m')  when 12  then c.totalAmount else  0 end  ) as '十二月',sum(c.totalAmount) as '总金额' FROM erp$sales$sales_order c LEFT JOIN erp$base$customer k ON c.customer_id=k.idLEFT JOIN erp$base$customer_category f  ON  k.customer_category_id =f.id LEFT JOIN erp$base$area a ON  c.area_id=a.idwhere (DATE_FORMAT(c.orderDate,'%Y')=2015 OR DATE_FORMAT(c.orderDate,'%Y')=2014) GROUP BY DATE_FORMAT(c.orderDate,'%Y');