mysql 行转列 （结果集以坐标显示）

 

create table capacity(
    type int ,
    numbers int ,
    monthst INT
);

 

 

select type,
sum(case monthst when 1 then numbers else 0 end ) 一月,
sum(case monthst when 2 then numbers else 0 end ) 二月,
sum(case monthst when 3 then numbers else 0 end ) 三月,
sum(case monthst when 4 then numbers else 0 end ) 四月,
sum(case monthst when 5 then numbers else 0 end ) 五月,
sum(case monthst when 6 then numbers else 0 end ) 六月,
sum(case monthst when 7 then numbers else 0 end ) 七月,
sum(case monthst when 8 then numbers else 0 end ) 八月,
sum(case monthst when 9 then numbers else 0 end ) 九月,
sum(case monthst when 10 then numbers else 0 end ) 十月,
sum(case monthst when 11 then numbers else 0 end ) 十一月,
sum(case monthst when 12 then numbers else 0 end ) 十二月
from capacity group by type;

 按type分组，并对每月的对应type的numbers求和。

select cap.type,sum(cap.a+cap.b+cap.c) 一季度,sum(cap.d+cap.e+cap.f) 二季度,sum(cap.g+cap.h+cap.i) 三季度,sum(cap.j+cap.k+cap.l) 四季度 from
    (select type,
    sum(case monthst when 1 then numbers else 0 end ) a,
    sum(case monthst when 2 then numbers else 0 end ) b,
    sum(case monthst when 3 then numbers else 0 end ) c,
    sum(case monthst when 4 then numbers else 0 end ) d,
    sum(case monthst when 5 then numbers else 0 end ) e,
    sum(case monthst when 6 then numbers else 0 end ) f,
    sum(case monthst when 7 then numbers else 0 end ) g,
    sum(case monthst when 8 then numbers else 0 end ) h,
    sum(case monthst when 9 then numbers else 0 end ) i,
    sum(case monthst when 10 then numbers else 0 end ) j,
    sum(case monthst when 11 then numbers else 0 end ) k,
    sum(case monthst when 12 then numbers else 0 end ) l
    from capacity group by type) cap 
group by cap.type;

 再对每行多列合并求和。