mysql——横表竖表相互转换-与横标转竖表关联查询

一  竖表转横表

1. 学生表成绩表-竖表

2. 查询数据

select * from student;

3. 想要将数据显示为横表, 即如下形式

数据显示为横表 sql语句:

select name as '姓名',
　　max(case subject when '语文' then result else 0 end) '语文',
　　max(case subject when '数学' then result else 0 end) '数学',
　　max(case subject when '英语' then result else 0 end) '英语'
from student group by name;

 

二, 横表变竖表

1.  创建横表 - 学生成绩表

2.  查询数据

select * from student1;

3. 将横表数据转换为竖表的形式, 即如下形式:

对应的sql语句:

select*from
(
  select 姓名 as Name , '语文' as Subject , 语文 as result from student1
  union all
  select 姓名 as Name , '数学' as Subject , 数学 as result from student1
  union all
  select 姓名 as Name , '物理' as Subject , 物理 as result from student1
) t
order by name;

 三，横表变竖表的-关联查询

 

 

查询出结果的sql:

SELECT cp.person_name,cm.url from cm_person cp  INNER JOIN  cm_midia cm on cp.id_front_ukid = cm.id
UNION
SELECT cp.person_name,cm.url from cm_person cp  INNER JOIN  cm_midia cm on cp.id_back_ukid = cm.id
UNION
SELECT cp.person_name,cm.url from cm_person cp  INNER JOIN  cm_midia cm on cp.id_header_ukid = cm.id;