横表转竖表
横表如下:
查询要求:
查询如下:(Rotate为横表名)
root@schuang 16:44 mysql>select year,'m1' as mouth,m1 as amount from Rotate
-> union all
-> select year,'m2' as mouth,m2 as amount from Rotate
-> union all
-> select year,'m3' as mouth,m3 as amount from Rotate
-> union all
-> select year,'m4' as mouth,m4 as amount from Rotate
-> order by amount;
查询结果:
将竖表转化为横表
竖表如下:
查询要求:
查询如下:(rotate为表名)
root@schuang 17:18 mysql>select year,
-> group_concat(case when month = 1 then amount else null end) as m1,
-> group_concat(case when month = 2 then amount else null end) as m2,
-> group_concat(case when month = 3 then amount else null end) as m3,
-> group_concat(case when month = 4 then amount else null end) as m4
-> from rotate group by year;
查询结果: