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

佚名 6年前 (2019-04-24) 随笔 1263人围观抢沙发百度已收录

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

SRE实战互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

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;