sql行列转换
业务里偶尔会遇到sql行列转换的问题，更多的是面试的时候会遇到...zzz
如下，1.把a表的结果集用sql显示成b表的形式 2.b=>a
select * from table a;+------+------+---------+| name | math | english |+------+------+---------+| anna | 80 | 97 || mike | 76 | 86 |+------+------+---------+select * from table b;+------+---------+-------+| name | subject | score |+------+---------+-------+| anna | shuxue | 80 || anna | yingyu | 97 || mike | shuxue | 76 || mike | yingyu | 86 |+------+---------+-------+--1.a=>b转换 select name,'shuxue' as subject,math as score from a union all select name,'yingyu' as subject ,english as score from a order by name;--2.b=>a转换 select name,max(math) as math,max(english)as english from( select name,case when subject='shuxue' then score end as math, case when subject='yingyu' then score end as english from b ) t group by name;
