业务里偶尔会遇到sql行列转换的问题,更多的是面试的时候会遇到...zzz
如下,1.把a表的结果集用sql显示成b表的形式 2.b=>a
sql cookbook里有好多例子,一本实用的工具书。
如下,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;
sql cookbook里有好多例子,一本实用的工具书。