# 有时候需要做报表时，如何将一个字段年份划分成12个月份呢？

select v_short_name,
sum(case when c_time = '201501' then n_amount else 0 end) as Jan,
sum(case when c_time = '201502' then n_amount else 0 end) as Feb,
sum(case when c_time = '201503' then n_amount else 0 end) as Mar,
sum(case when c_time = '201504' then n_amount else 0 end) as Apr,
sum(case when c_time = '201505' then n_amount else 0 end) as May,
sum(case when c_time = '201506' then n_amount else 0 end) as Jun,
sum(case when c_time = '201507' then n_amount else 0 end) as Jul,
sum(case when c_time = '201508' then n_amount else 0 end) as Aug,
sum(case when c_time = '201509' then n_amount else 0 end) as Sep,
sum(case when c_time = '201510' then n_amount else 0 end) as Oct,
sum(case when c_time = '201511' then n_amount else 0 end) as Nov,
sum(case when c_time = '201512' then n_amount else 0 end) as Decn,
sum(n_amount) as Total
from XXX

group by v_short_name;

SET @asql='';
SELECT DISTINCT c_time,
@asql:=CONCAT(@asql,CONCAT('MAX(CASE DATE(c_time) WHEN \'',DATE(c_time),'\' THEN n_amount ELSE 0 END) AS ',DATE(c_time)),',')
FROM 表名;
SET @asql=CONCAT('SELECT v_short_name 'LEFT(@asql,LENGTH(@asql)-1),' FROM 表名GROUP BY v_short_name');
SELECT @asql;
PREPARE stml FROM @asql;
EXECUTE stml;

• 本文已收录于以下专栏：

举报原因： 您举报文章：有时候需要做报表时，如何将一个字段年份划分成12个月份呢？ 色情 政治 抄袭 广告 招聘 骂人 其他 (最多只允许输入30个字)