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

create table capacity(

typeint,

numbersint,

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 0end ) a,

sum(case monthst when 2 then numbers else 0end ) b,

sum(case monthst when 3 then numbers else 0end ) c,

sum(case monthst when 4 then numbers else 0end ) d,

sum(case monthst when 5 then numbers else 0end ) e,

sum(case monthst when 6 then numbers else 0end ) f,

sum(case monthst when 7 then numbers else 0end ) g,

sum(case monthst when 8 then numbers else 0end ) h,

sum(case monthst when 9 then numbers else 0end ) i,

sum(case monthst when 10 then numbers else 0end ) j,

sum(case monthst when 11 then numbers else 0end ) k,

sum(case monthst when 12 then numbers else 0end ) l

from capacity group by type) cap

group by cap.type;

再对每行多列合并求和。