sql语句实现行转列练习

 

create table STUDENT_SCORE
(
name VARCHAR2(20),
subject VARCHAR2(20),
score NUMBER(4,1)
);
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '语文', 78.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '数学', 88.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '英语', 98.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '语文', 89.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '数学', 76.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '英语', 90.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '语文', 99.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '数学', 66.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '英语', 91.0);

select * from student_score;

/*
4.1得到类似下面的结果
姓名 语文 数学 英语
王五 89 56 89
李四 xx xx xx

*/

数据库查询中用到的关键词主要包含六个，他们的顺序依次为 

  select--from--where--group by--having--order by 

语句的执行顺序

 from--where--group by--having--select--order by,

--连表查询

select t1.name "姓名", t1.score "语文", t2.score "数学", t3.score "英语"
from (select name, score from student_score where subject = '语文') t1,
(select name, score from student_score where subject = '数学') t2,
(select name, score from student_score where subject = '英语') t3
where t1.name = t2.name
and t1.name = t3.name;

思路

通过观察结果，先把每列的结果查询出来，在把每个子表关联起来。

--分组查询
select name "姓名",
max(decode(subject, '语文', score)) "语文",
max(decode(subject, '数学', score)) "数学",
max(decode(subject, '英语', score)) "英语"
from student_score
group by name;

 

思路

用name分组 获得每个人的成绩，每组数据在通过decode匹配指定的数据，然后通过max函数过滤 输出