mysql 横向查询12个月数据

需求是获取当年度每月数据，一开始是这样写的

SELECT o.dest_country,o.customer_name,kef.product_name,1 AS `type`,
CASE MONTH(kef.created_time) WHEN'1' THEN SUM(kef.quantity) ELSE 0 END AS month1,
CASE MONTH(kef.created_time) WHEN'2' THEN SUM(kef.quantity) ELSE 0 END AS month2,
CASE MONTH(kef.created_time) WHEN'3' THEN SUM(kef.quantity) ELSE 0 END AS month3,
CASE MONTH(kef.created_time) WHEN'4' THEN SUM(kef.quantity) ELSE 0 END AS month4,
CASE MONTH(kef.created_time) WHEN'5' THEN SUM(kef.quantity) ELSE 0 END AS month5,
CASE MONTH(kef.created_time) WHEN'6' THEN SUM(kef.quantity) ELSE 0 END AS month6,
CASE MONTH(kef.created_time) WHEN'7' THEN SUM(kef.quantity) ELSE 0 END AS month7,
CASE MONTH(kef.created_time) WHEN'8' THEN SUM(kef.quantity) ELSE 0 END AS month8,
CASE MONTH(kef.created_time) WHEN'9' THEN SUM(kef.quantity) ELSE 0 END AS month9,
CASE MONTH(kef.created_time) WHEN'10' THEN SUM(kef.quantity) ELSE 0 END AS month10,
CASE MONTH(kef.created_time) WHEN'11' THEN SUM(kef.quantity) ELSE 0 END AS month11,
CASE MONTH(kef.created_time) WHEN'12' THEN SUM(kef.quantity) ELSE 0 END AS month12
FROM `sms_order` o
LEFT JOIN `sms_order_product` kef ON kef.order_id = o.id

后来发现数据是有问题的，计算的总和只要碰到月份有值，就会在当月直接sum值，之后月份的数据就没有了。全部集中在第一个判断有值的月份，我看网上有很多是这样写的，这是不对的。查出数据如下图：

 

正确写法应该是这样：

SELECT o.dest_country,o.customer_name,kef.product_name,1 AS `type`,
SUM(CASE MONTH(kef.created_time) WHEN'1' THEN kef.quantity ELSE 0 END) AS month1,
SUM(CASE MONTH(kef.created_time) WHEN'2' THEN kef.quantity ELSE 0 END) AS month2,
SUM(CASE MONTH(kef.created_time) WHEN'3' THEN kef.quantity ELSE 0 END) AS month3,
SUM(CASE MONTH(kef.created_time) WHEN'4' THEN kef.quantity ELSE 0 END) AS month4,
SUM(CASE MONTH(kef.created_time) WHEN'5' THEN kef.quantity ELSE 0 END) AS month5,
SUM(CASE MONTH(kef.created_time) WHEN'6' THEN kef.quantity ELSE 0 END) AS month6,
SUM(CASE MONTH(kef.created_time) WHEN'7' THEN kef.quantity ELSE 0 END) AS month7,
SUM(CASE MONTH(kef.created_time) WHEN'8' THEN kef.quantity ELSE 0 END) AS month8,
SUM(CASE MONTH(kef.created_time) WHEN'9' THEN kef.quantity ELSE 0 END) AS month9,
SUM(CASE MONTH(kef.created_time) WHEN'10' THEN kef.quantity ELSE 0 END) AS month10,
SUM(CASE MONTH(kef.created_time) WHEN'11' THEN kef.quantity ELSE 0 END) AS month11,
SUM(CASE MONTH(kef.created_time) WHEN'12' THEN kef.quantity ELSE 0 END) AS month12
FROM `sms_order` o
LEFT JOIN `sms_order_product` kef ON kef.order_id = o.id

sum函数放在前，满足条件的话，只取需要累计的字段，然后再sum计算，最终得出的数据就是正确的。如下图：

 这是在最近的数据整合时，书写SQL碰到的问题和解决方案，希望对有同样需求的伙伴有一定的帮助。