MySQL中将横表查询成竖表和竖表查询成横表

横表转竖表

横表如下：

 查询要求：

 查询如下：（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;

查询结果：