这是年中详细月查询
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.id
LEFT JOIN erp$base$customer_category f ON k.customer_category_id =f.id
LEFT JOIN erp$base$area a ON c.area_id=a.id
where DATE_FORMAT(c.orderDate,'%Y')=2015
UNION 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.id
LEFT JOIN erp$base$customer_category f ON k.customer_category_id =f.id
LEFT JOIN erp$base$area a ON c.area_id=a.id
where 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.id
LEFT JOIN erp$base$customer_category f ON k.customer_category_id =f.id
LEFT JOIN erp$base$area a ON c.area_id=a.id
where (DATE_FORMAT(c.orderDate,'%Y')=2015 OR DATE_FORMAT(c.orderDate,'%Y')=2014) GROUP BY DATE_FORMAT(c.orderDate,'%Y');